How to remove 0 Data in Cross Table Load

[syedsaqib]

Hi Everyone,

I am converting multiple fields of "Aging groups" from Columns to Rows using cross table as shown in attached screenshot.

There are many 0 values in the data (yellow highlighted) and all these '0's are also being loaded to final table after applying crosstable.

How can I load the same data with crosstable ignoring all "0" values ?

Thanks

[Qrishna]

Sample Data and desired output pls?

[syedsaqib]

Hi, Sample data and desired output attached.

[MayilVahanan · Accepted solution]

Hi @syedsaqib 

Try like below

T1:
CrossTable(AgingGroup, Amount1)
LOAD [Customer No],
Current,
[1-30],
[31-60],
[61-90],
[91-180],
[181-365],
[Over 365]
FROM
[D:\Qlik\Com\Sample Data and Desired result.xlsx]
(ooxml, embedded labels, table is Sheet2);

NoConcatenate
Load * Resident T1 Where (Amount1 <>0);

DROP Table T1;