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: 
MuraliPrasath
Creator III
Creator III

Comparing values from the same field using filters.

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)". 

MuraliPrasath_1-1595172698489.png     

MuraliPrasath_2-1595172795877.png

 

 

 

1 Solution

Accepted Solutions
Kushal_Chawda

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;

 

Annotation 2020-07-20 010956.png

 

View solution in original post

4 Replies
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.

commQV50.PNG

Kushal_Chawda

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;

 

Annotation 2020-07-20 010956.png

 

MuraliPrasath
Creator III
Creator III
Author

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...