topic Re: Cross Table Script Help in QlikView

Cross Table Script Help

ivandrago — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Cross Table Script Help

sunny_talwar — Wed, 24 Oct 2018 15:56:13 GMT

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;

Re: Cross Table Script Help

ivandrago — Wed, 24 Oct 2018 16:03:12 GMT

Excellent, Thanks!