topic Re: Cross table not working in QlikView

Cross table not working

shane_spencer — Sat, 16 Nov 2024 04:10:36 GMT

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)

Re: Cross table not working

shiveshsingh — Thu, 14 Mar 2019 10:42:21 GMT

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

Re: Cross table not working

shane_spencer — Thu, 14 Mar 2019 10:44:46 GMT

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.

Re: Cross table not working

marcus_sommer — Thu, 14 Mar 2019 13:20:23 GMT

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

Re: Cross table not working

shane_spencer — Thu, 14 Mar 2019 13:46:48 GMT

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

Re: Cross table not working

marcus_sommer — Thu, 14 Mar 2019 14:10:16 GMT

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