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: 
harsha
Creator
Creator

Compare two itemIDs' values and highlight with flag

Hi guys
Need a help

EX:

 

Company ID:4129034
Year: 2017

DataID: 60634843286 and Value : 287966000
DataID: 60634843285 and Value :145077000

Rule :
60634843286>60634843285

 

I need to compare two Data points in the same company and within year, if one is value is greater than other value, then those company IDs must have some flag

Or I want to highlight those companies in graph (Image Attached )

 

your help is highlighy appreciated as it will crack for 12 similar cases

1 Solution

Accepted Solutions
Saravanan_Desingh

Check this.

tab1:
LOAD RowNo() As RowID,* INLINE [
    CompanyID, PointID, Year, Value
    635939, 5290, 2017, 350
    635939, 5291, 2017, 250
    635939, 5290, 2018, 200
    635939, 5291, 2018, 300
    635939, 5290, 2019, 420
    635939, 5291, 2019, 280
    516278, 5290, 2017, 810
    516278, 5291, 2017, 850
    516278, 5290, 2018, 460
    516278, 5291, 2018, 340
    516278, 5290, 2019, 290
    516278, 5291, 2019, 310
    635939, 6320, 2017, 460
    635939, 6321, 2017, 320
    635939, 6320, 2018, 650
    635939, 6321, 2018, 750
    635939, 6320, 2019, 820
    635939, 6321, 2019, 600
    516278, 6320, 2017, 270
    516278, 6321, 2017, 350
    516278, 6320, 2018, 510
    516278, 6321, 2018, 750
    516278, 6320, 2019, 470
    516278, 6321, 2019, 360
];

tab2:
LOAD CompanyID, Year, Value As Value1
Resident tab1
Where PointID=5290;

Left Join(tab2)
LOAD CompanyID, Year, Value As Value2
Resident tab1
Where PointID=5291;

Left Join(tab1)
LOAD CompanyID, Year, If(Value1<Value2, 'Yes','No') As Glsag
Resident tab2;

Drop Table tab2;

View solution in original post

29 Replies
JustinDallas
Specialist III
Specialist III

Do you have any dummy data that we can play with?  Also a table representation of what you'd like to achieve would be nice.

Saravanan_Desingh

One solution is.

tab1:
LOAD * INLINE [
    Company ID, Year, DataID, Value
    4129034, 2017, 60634843286, 287966000
    4129034, 2017, 60634843285, 145077000
    4128912, 2018, 60634843285, 1
    4128912, 2018, 60634843285, 2
    4128914, 2019, 60634843284, 8
    4128914, 2019, 60634843284, 16
];

Left Join(tab1)
LOAD [Company ID], Year, If(Evaluate(Concat(DataID,'<>')),'Y','N') As Flag
Resident tab1
Group By [Company ID], Year;
Saravanan_Desingh

Output.

commQV15.PNG

harsha
Creator
Creator
Author

Hi @JustinDallas , thank u for the quick comment. I have attached a dummy data , rule that i want to apply and the desired output as well. 

Do let me know if you require any further info. Thank u again.

harsha
Creator
Creator
Author

Hi @Saravanan_Desingh , 

Appreciate your faster response. However, I want to compare the value of pointID (60634843286) with other value of pointID (60634843285)

Ex:

CompanyID: 4129034, year: 2017, value of pointID: 60634843286 is 287966000 greater than the 
CompanyID: 4129034, year: 2017, value of pointID: 60634843285 is 145077000


I have attached a dummy data,rules and desired output as well. Would you help me to get it pls? 

Saravanan_Desingh

Check this.

tab1:
LOAD RowNo() As RowID,* INLINE [
    CompanyID, PointID, Year, Value
    635939, 5290, 2017, 350
    635939, 5291, 2017, 250
    635939, 5290, 2018, 200
    635939, 5291, 2018, 300
    635939, 5290, 2019, 420
    635939, 5291, 2019, 280
    516278, 5290, 2017, 810
    516278, 5291, 2017, 850
    516278, 5290, 2018, 460
    516278, 5291, 2018, 340
    516278, 5290, 2019, 290
    516278, 5291, 2019, 310
    635939, 6320, 2017, 460
    635939, 6321, 2017, 320
    635939, 6320, 2018, 650
    635939, 6321, 2018, 750
    635939, 6320, 2019, 820
    635939, 6321, 2019, 600
    516278, 6320, 2017, 270
    516278, 6321, 2017, 350
    516278, 6320, 2018, 510
    516278, 6321, 2018, 750
    516278, 6320, 2019, 470
    516278, 6321, 2019, 360
];

tab2:
LOAD CompanyID, Year, Value As Value1
Resident tab1
Where PointID=5290;

Left Join(tab2)
LOAD CompanyID, Year, Value As Value2
Resident tab1
Where PointID=5291;

Left Join(tab1)
LOAD CompanyID, Year, If(Value1<Value2, 'Yes','No') As Glsag
Resident tab2;

Drop Table tab2;
Saravanan_Desingh

Output:

commQV19.PNG

harsha
Creator
Creator
Author

Hi @Saravanan_Desingh 

Thank you for the solution. However, my data is huge and i have other pointIDs as well with text data. So, what i did was i took the required pointIDs and converted text into '0'.

 

 

I have applied your solution in load script and took a table to see the results. in the table, I took all the required fields which consists of all pointIDs, in order to avoid all and show i took pointID in measure and applied a set analysis .

 

 

But the results are not as expected. In addition, i see some garbage(duplicated) values in the table. also, Yes or no in the flag is not working.

 

Pls help  

Saravanan_Desingh

Can you check this?

tab1:
LOAD RowNo() As RowID,* INLINE [
    CompanyID, PointID, Year, Value
    635939, 5290, 2017, 350
    635939, 5291, 2017, 250
    635939, 5290, 2018, 200
    635939, 5291, 2018, 300
    635939, Tex1, 2018, 300    
    635939, 5290, 2019, 420
    635939, 5291, 2019, 280
    516278, 5290, 2017, 810
    516278, 5291, 2017, 850
    516278, 5290, 2018, 460
    516278, 5291, 2018, 340
    516278, 5290, 2019, 290
    516278, 5291, 2019, 310
    635939, 6320, 2017, 460
    635939, 6321, 2017, 320
    635939, 6320, 2018, 650
    635939, 6321, 2018, 750
    635939, 6320, 2019, 820
    635939, 6321, 2019, 600
    516278, 6320, 2017, 270
    516278, 6321, 2017, 350
    516278, 6320, 2018, 510
    516278, 6321, 2018, 750
    516278, 6320, 2019, 470
    516278, 6321, 2019, 360
];

tab2:
LOAD CompanyID, Year, Value As Value1
Resident tab1
Where PointID=5290;

Left Join(tab2)
LOAD CompanyID, Year, Value As Value2
Resident tab1
Where PointID=5291;

Left Join(tab1)
LOAD CompanyID, Year, 5290 As PointID, If(Value1<Value2, 'Yes','No') As Glsag
Resident tab2;

Left Join(tab1)
LOAD CompanyID, Year, 5291 As PointID, If(Value1<Value2, 'Yes','No') As Glsag
Resident tab2;

Drop Table tab2;