cast of date column in "concat" not consistent

3713479

Version in use is 11.1.1.7

There are two analyses in the dashboard.  

The first analysis does a concatenation of an ID and a cast of a date column generating the format DD-MON-YY for the date.

The second analysis in the dashboard also does a concatenation of an ID and a cast of a date column generating the format

YYYY/MM/DD HH:MM:SS

The two analyses are downloaded to Excel, but cannot be merged using Power Query because this generates a mismatch.

Any suggestions offered are appreciated.

[Deleted User]

a) You're data dumping to Excel to do something that OBI can do itself? What a waste.

b) Have you checked the exact data types of the two columns? What is their EXACT definition the RPD and in the DB?

c) It's analytics. It's tech. It does what you tell it to do. If your definitions are imprecise so will be the result.

3713479

Christian

The same column in both analyses.

Note that the second analysis invokes the MIN aggregate function on the date column.

This would allow me to identify one transaction among many payments to a vendor where I could display a 'care days' column only one time rather than repeating it.

The 'suppress' option does not work for fact tables based on documentation.

I was using Excel as a workaround as I was not able to find a way to display the 'care days' column only one time for each vendor.

3713479

Christian

The same column in both analyses.

Note that the second analysis invokes the MIN aggregate function on the date column.

This would allow me to identify one transaction among many payments to a vendor where I could display a 'care days' column only one time rather than repeating it.

The 'suppress' option does not work for fact tables based on documentation.

I was using Excel as a workaround as I was not able to find a way to display the 'care days' column only one time for each vendor.

[Deleted User]

It would be good if you provided that kind of information with out initial question and be even more specific: What are the exact column formulae?

a) That a column (whatever the column is - you're not stating that) and the same column with MIN produce different results doesn't make sense

b) Test and verify what EXACTLY the column and the column with MIN bring back

3713479 wrote:I was using Excel as a workaround as I was not able to find a way to display the 'care days' column only one time for each vendor.

That's yet another different problem and more points to a requirement being approached with an inadequate solution approach.

3713479

Christian

The analysis generates 25+ output columns detailing payments to each vendor for a date range.

There may be 25 payments to each vendor, but the requirement is to report the "care days" for that vendor/date range only once with the other rows showing null values to avoid overstating that value in the aggregate.

I can isolate the "care days" for the date range, but the challenge is to integrate that value into the main report as a single row  for each vendor.

Thanks for any insights you may have on a solution.

Marco Poloni

Hi,

I'm not sure I get what are you trying to do - an example would help since I suppose I have done something similar already.

About the original question: why are you performing a cast of the same column in 2 different formats? Or I'm getting it wrong?

I did quite a bit of work with dates, cast of dates in different formats and exports in Excel but I do not clearly understand the issue.

I will try to reproduce it.

Ciao

MP

3713479

Marco

Thanks for the note...

The objective is to display a column ( "care days" ) only once for each vendor/billing date when there are multiple payment transactions for that vendor/bill date.  Otherwise, the column is repeated with the same value and the aggregate value for the column is over stated.

The documentation indicates that the suppress column functionality does not apply to "facts" tables.

One report would create one key for each vendor/bill date based on the a concatenation of the date and other columns using the aggregate function ( MIN).

The main report would perform the concatenation for  each row.  Using a "case' statement, I can populate the column when the keys matched and leave the column null when there is no match.

The two programs were not producing the same date format so the match was not working.

I can report back either 4-20 or 4-21 after further testing.

Jim

3713479

Marco

One discovery is that applying an aggregate formula to the date column converts it to the format YYYY/MM/DD HH:MM:SS 

Without applying the aggregate formula, the format is DD-MON-YY

Jim