Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal will move to Qlik Community Oct 4. Temporary case outage Oct 3. READ DETAILS
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

 

View solution in original post

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