Crosstable Dates disappear

[rduah4u]

Hi, 

I have an existing Dates table and I also have some data from an excel file that contains dates which im trying to align with my main dates table to interact dynamically and pull the right data for the required dates. 

The date field in my main Dates table is called DatesTrans - when i look at my model both tables are joined by the DatesTrans but i dont see any dates in the Data table - Example below:

BlockID, DatesTrans, Blocks

101             -                  2

103             -                  5

tmpdata:
Crosstable (Date, Block,1)
LOAD
*
FROM XYZ
;

Data:
NoConcatenate
LOAD

BlockID, 
date(num#(Date)) AS DateTrans,
Blocks
Resident tmpdata;
Drop Table tmpdata;
Exit Script;

I'm not sure if it has anything to do with the fact that the DateTrans field in my Dates table is an Interger in the DW formatted as 20240131. This is the main field used across all tables/reporting.

[lennart_mo]

Hi @rduah4u,

Using the .csv you provided i noticed, that the dates had some blankspace in front of them.

This is the formula i came up with to give you your desired integer to match with the field TransDate:

Num#(Date(Trim(Date), 'YYYYMMDD')), 'YYYYMMDD') as TransDate

Trim() to remove the whitespace, Date() to adjust the format, to get the integer use num#(Date, 'YYYYMMDD'), it transforms the date into an integer.

Hope this works for you!

[marcus_sommer · Accepted solution]

The blank-char isn't a space-char else it's a tab-char respectively chr(9). I'm not sure if it's fetched by trim() but the following should be working:

date(date#(purgechar(Date, chr(9)),'YYYY-MM-DD'),'YYYYMMDD') AS DateTrans

[lennart_mo · Accepted solution]

Trim() seemed to work for me, but purgechar() for sure is safer to use.

I think your suggested formula might pose another problem though, since the DateTrans from @rduah4u's table seems to be just an integer with no date-tag, so i would encapsulate your entire formula in a num#() to get the date-text as an integer and not the date-value.

[rduah4u]

Thank you Marcus & Lennart, that seems to have done the trick!