topic How to remove 0 Data in Cross Table Load in Connectivity & Data Prep

How to remove 0 Data in Cross Table Load

syedsaqib — Sun, 16 May 2021 04:11:53 GMT

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

Re: How to remove 0 Data in Cross Table Load

Qrishna — Sun, 16 May 2021 06:31:47 GMT

Sample Data and desired output pls?

Re: How to remove 0 Data in Cross Table Load

syedsaqib — Sun, 16 May 2021 23:43:25 GMT

Hi, Sample data and desired output attached.

Re: How to remove 0 Data in Cross Table Load

MayilVahanan — Mon, 17 May 2021 03:07:52 GMT

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;