Hi all! I have a spreadsheet of reimbursements- where every transaction has it's own row (see below):
I'd like to combine the data so each person gets their own row, and every amount and date for that person becomes it's own column:
How might I transform the data in the load script to accomplish this?
use cross table
I think you would need Generic Load here: The Generic Load
SubField(Amt, '|', 1) as Amt1,
SubField(Amt, '|', 2) as Amt2,
SubField(Amt, '|', 3) as Amt3,
SubField(Date, '|', 1) as Date1,
SubField(Date, '|', 2) as Date2,
SubField(Date, '|', 3) as Date3;
Concat(Amount, '|', RecNo()) as Amt,
Concat(Date, '|', RecNo()) as Date
(html, codepage is 1252, embedded labels, table is @1)
Group By Name;
could you not just use the pivot table object to display the content that way? Altering how you load your data for visualisation purposes beyond the standard fact vs dimension model discussions seems the wrong approach unless I'm missing something.