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: 
shane_spencer
Specialist
Specialist

Cross table not working

I'm trying to do a Cross Table load on the spreadsheet. For some reason it's not working, it's just doing a straight load even though the Wizard preview shows correct. It just does a straight load, what am I missing? PFA example

QUARTERS_CROSS:
CrossTable(TAX_NUMBER, MONTH, 0)
LOAD *
FROM QUARTERS.xlsx
(ooxml, embedded labels, table is QUARTERS)

 

crosstable.PNG

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

Try it in this way:

QUARTERS_CROSS:
CrossTable(TAX_NUMBER, MONTH, 1)
LOAD 'Dummy' as Dummy, * FROM QUARTERS.xlsx (ooxml, embedded labels, table is QUARTERS)

drop fields Dummy;

- Marcus

View solution in original post

5 Replies
shiveshsingh
Master
Master

Hi

What's your expected output? I guess your crosstable should work fine in script. 

shane_spencer
Specialist
Specialist
Author

The expected output is as per the preview. A list of Tax Numbers and Months, so Instead of the Tax Numbers being fields they will all be in one field with an entry for each month. For some reason it's just not happening. 

marcus_sommer

Try it in this way:

QUARTERS_CROSS:
CrossTable(TAX_NUMBER, MONTH, 1)
LOAD 'Dummy' as Dummy, * FROM QUARTERS.xlsx (ooxml, embedded labels, table is QUARTERS)

drop fields Dummy;

- Marcus

shane_spencer
Specialist
Specialist
Author

That worked. Any idea why mine didn't? it seems a little strange.

marcus_sommer

A crosstable-resolution needs in Qlik at least one column/dimension on which the transposing is performed and if there is none a simply added dummy-field will do the job. I don't know the technically details how ths feature are designed and I could imagine ways which wouldn't need such a field but I assume that there is a good reason for it.

- Marcus