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.

[Padma123]

num#(Date) as DateTrans

[rduah4u]

num#(Date, 'YYYYMMDD') AS DateTrans - When i apply this the format is still in it's original context 2024-04-01 Whereas i need it as YYYYMMDD?

[Padma123]

date(NUM#(date),'YYYYMMDD')

[rduah4u]

Unfortunately it disappears again! 

[marcus_sommer]

Try it with: date(date#(YourField),'YYYYMMDD'), 'YYYYMMDD')

[rduah4u]

Still no good! Have no idea what this could be for something really seemingly simple. I've attached the CSV file. As previously mentioned i have a sperate QVD File for my Dates table

Dates:
LOAD
DateTrans
FROM [XYZqvd]
(qvd);

tmpdata:
Crosstable (Date, AppData,1)
LOAD
*
FROM [XYZ.csv]
;

Data:
NoConcatenate
LOAD
date(date#(Date,'YYYYMMDD'),'YYYYMMDD') AS DateTrans,
AppData AS BLOCK
Resident tmpdata;

Drop Table tmpdata;
Exit Script;

[marcus_sommer]

Have you adapted YourField to your existing field?

The logic itself is simple - convert the string with the appropriate format-pattern and then applying the wanted formatting again with the format-pattern or without in which the default formatting is applied. You may take a look on the interpretation-variables at the begin of the script which are the base for the interpretation.

[rduah4u]

I've tried this several times in various ways. Attached is an example of the csv file. 

My DateTrans field in the DW is (PK, int, not Null) - Will be grateful if you can try and replicate:

Dates:
LOAD

DateTrans
FROM [XYZ.qvd]
(qvd);

tmpdata:
Crosstable (Date, AppData,1)
LOAD
*
FROM [XYZ.csv]
;

Data:
NoConcatenate
LOAD

BLOCKID,
date(date#(Date,'YYYYMMDD'),'YYYYMMDD') AS DateTrans,
AppData AS BLOCK
Resident tmpdata;

Drop Table tmpdata;
Exit Script;

[marcus_sommer]

The format from date within the csv is different to your description and not YYYYMMDD else YYYY-MM-DD and therefore the transformation should be look like:

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