Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ivandrago
Creator II
Creator II

Cross Table Script Help

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

2 Replies
sunny_talwar

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
Creator II
Creator II
Author

Excellent, Thanks!