Static date in header, needs to be loaded as a dynamic date to be used in a crossable.

[varmekontrol]

Hi, 

Maybe this question has already been answered, but I have not been able to understand the answer to the question.

So hopefully, someone can dumb it down for me 🙂 

I have some data that is refreshed on a daily basis, so the dates change on a regular basis. 

I want to make a crosstable, but the only way I know how to make a crosstable is with static headers.  

I have a .csv file with the following data.  (Maybe there are already some small errors in the syntax, I have not been able to test with dynamic Fields/Dates) 

////***************** Normal Load *****************\\
Temp: 
Load * Inline [

ID,Product,08-06-2022,15-06-2022,30-06-2022,15-07-2022,31-07-2022,15-08-2022,31-08-2022,08-09-2022
09682,Enh.,378,378,378,378,378,378,378,378
09687,Enh.,,0,0,0,0,0,0,
09690,Enh.,,0,0,0,0,0,0,
09695,Enh.,,0,0,0,0,0,0,
09698,Enh.,,0,0,0,0,0,0,
];

//***************** CrossTable Load *****************\\

NoConcatenate

TempCross:
CrossTable(Date, Value, 2)
LOAD *,
	08-06-2022,	
	15-06-2022,
	30-06-2022,
	15-07-2022,
	31-07-2022,
	15-08-2022,
	31-08-2022,
	08-09-2022

Resident Temp;

What I want to have as a result.  (Dates here are static, but need to be able to change depending on the data)

ID	Product	Date	Value
09682	Enh	08-06-2022	378
09682	Enh	15-06-2022	378
09682	Enh	30-06-2022	378
09682	Enh	15-07-2022	378
09682	Enh	31-07-2022	378
09682	Enh	15-08-2022	378
09682	Enh	31-08-2022	378
09682	Enh	08-09-2022	378

[rubenmarin · Accepted solution]

Hi, You can just do 

CrossTable(Date, Value, 2)
LOAD *
Resident Temp;

The table should have the 2 first columns/fields as the dimensions fields (ID and Product) and the other fields should be the dates. If the table has this, you can just use the *.

[varmekontrol]

What if I were to replace the inline data with a real import load? 

////***************** Normal Load *****************\\
Temp: 
LOAD @1,  //ID, 
     @2,  //Product, 
     @12, //Date 1
     @13, //Date 2
     @14, //Date 3
     @15, //Date 4
     @16, //Date 5
     @17  //Date 6
FROM
[C:\Temp\*.csv]
(biff, no labels, table is Data$);

//***************** CrossTable Load *****************\\

TempCross:
CrossTable(Date, Value, 2)
LOAD *
Resident Temp;

Do you have a suggestion on how to load the Temp data if the dates were to be dynamic? 
If I use the above load with no labels, it inserts the date-headers as values in the crosstable

[varmekontrol]

Wow, that was actually so simple, that it hurt my pride... 

But it worked. 

Many thanks. 

[rubenmarin]

Hi, I'm not sure if this post was before or after you found the solution, just in case, I think you can merge in one sentence:

CrossTable(Date, Value, 2)
LOAD *
FROM
[C:\Temp\*.csv]
(biff, no labels, table is Data$);

[varmekontrol]

Hi,

This was after the solution.
My question was only about the crosstable, so this was an added question.

Your suggestion for this worked as well.. 

Thank you once again.