Comparing values from the same field using filters.

[MuraliPrasath]

Hi Qlik,

I got struck with the following method, I have attached the example data too.

Can someone help me on Data-Modelling.  I need have two filters like "Products(old)" and "Products(New)". 

     

[Saravanan_Desingh]

One solution is.

Mapper:
Mapping
LOAD F1, '@start@'&F2&'@end@' INLINE [
	F1, F2
    New, Product(New)
    Old, Product(Old)
];

tab1:
Generic
LOAD Country, Products, TextBetween(MapSubString('Mapper',Products),'@start@','@end@')  As Product, Totals;
LOAD Country, 
     Products, 
     Totals
FROM
[C:\Users\sarav\Downloads\Data (1).xlsx]
(ooxml, embedded labels, table is Data);

[Saravanan_Desingh]

Output.

[Kushal_Chawda · Accepted solution]

If I understood correctly then try below

Data:
LOAD
    Country,
    Products,
    Totals
FROM [lib://Data/Data (1).xlsx]
(ooxml, embedded labels, table is Data);

All_Products:
Load Distinct Country 
Resident Data;

Left Join(All_Products)
Load Distinct
    SubField(trim(Products),'(',1) as All_Products
Resident Data;

Left Join(All_Products)
Load Distinct Country,
     SubField(trim(Products),'(',1) as All_Products,
     SubField(trim(Products),'(',1) as [Products(Old)],
     Totals as  [Products(Old) Total]
Resident Data
where index(lower(trim(Products)),'(old)');

Left Join(All_Products)
Load Distinct Country,
     SubField(trim(Products),'(',1) as All_Products,
     SubField(trim(Products),'(',1) as [Products(New)],
     Totals as  [Products(New) Total]
Resident Data
where index(lower(trim(Products)),'(new)');

Drop Table Data;

Drop Field All_Products;

Final:
NoConcatenate
Load Country,
     if(len(trim([Products(Old)]))=0,'Null',[Products(Old)]) as [Products(Old)],
     if(len(trim([Products(New)]))=0,'Null',[Products(New)]) as [Products(New)],
     alt([Products(Old) Total],0) as [Products(Old) Total],
     alt([Products(New) Total],0) as [Products(New) Total]
Resident All_Products;

Drop Table All_Products;

[MuraliPrasath]

Hi Kush,

Thanks for you effort on this. Highly appreciated.

I've similar requirement, can you help me on this too. thanks!

https://community.qlik.com/t5/New-to-Qlik-Sense/Compare-values-from-the-same-field-using-filters/m-p...