Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 *.
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 *.
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
Wow, that was actually so simple, that it hurt my pride...
But it worked.
Many thanks.
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$);
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.