Cross Table Script Help

[ivandrago]

Hi,

I have the attached Document, which looks at the spreadsheet called Crosstable.xlsm.

The Document shows two tables, one that looks at the worksheet Before and another table which looks at the worksheet After

I am trying to get the worksheet Before to have the end result to look like the worksheet After, I have tried to use the Crosstable script function but it kept on failing with errors.

Any ideas on how to achieve this?

Thanks

[sunny_talwar · Accepted solution]

Try this

Before:
LOAD NAME,
VALUE,
REPORT,
UNIVERSE
FROM Crosstable.xlsm
(ooxml, embedded labels, table is Before);

New:
LOAD Distinct
REPORT,
UNIVERSE
Resident Before;

FOR i = 1 to FieldValueCount('NAME')

LET vName = FieldValue('NAME', $(i));

Left Join (New)
LOAD Distinct
REPORT,
UNIVERSE,
VALUE as [$(vName)]
Resident Before
Where NAME = '$(vName)';

NEXT

DROP Table Before;

[ivandrago]

Excellent, Thanks!