Compare two itemIDs' values and highlight with flag

[harsha]

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

[stevedark]

Hi @harsha 

You refer to both PointID and DataPointID in your code, make sure you get this right in the WHERE EXISTS and INLINE table.

Comp1A-Comp1B gives you the difference between the two points, zero if they are the same and a positive or negative value if not.

The way you are creating the flag for one being less than the other is fine, note that you have one expression with less than and the other less than or equal to.

Glad that you are making progress!

[harsha]

also, when i applied this, application shows fields with flag which are unnecessary also . pls help

[harsha]

Yessss... i making some progress with your code... 😍

sorry Steve, that's a mistake from my end while posting. Yes, i have only PointID.

Do i need to use

"Comp1A-Comp1B"  even if i want to see only the "one being less than the other "?

"LOAD
*,
Comp1A - Comp1B as Comp1Diff, if(Comp1A <= Comp1B, 'Yes', 'No') as Flag_1,
Comp2A - Comp2B as Comp2Diff, if(Comp2A < Comp2B, 'Yes', 'No') as Flag_2
;
LOAD
CompanyID,
[Time Series Year],
sum(if(PointID = 60684989045, VALUE, 0)) as Comp1A,
sum(if(PointID = 60684986288, VALUE, 0)) as Comp1B,
sum(if(PointID = 4300689, VALUE, 0)) as Comp2A,
sum(if(PointID = 60684986288, VALUE, 0)) as Comp2B

Resident RAWDATA

WHERE EXISTS (PointID)
GROUP BY
Company,
[Year]

[stevedark]

You can take out the Diff fields, I just thought it would be useful to see what the difference is.

I'm confused how you get that table after using the load script I sent, as this would not have the points in the data model, or VALUE, just the Comp fields and Flags.

[harsha]

hey @stevedark,  i loaded the script and took the required fields into a table on dashboard to see the results are matching with my requirement or not. Sorry, if i created any confusion here.

And i'm trying the script now and hopefully, this should work. will keep you posted 🙂

[harsha]

@stevedark ... my bad luck.. even after repeated attempts... result is still having unwanted comparison is being done 😔

Problem is i'm getting the accurate results however, some fields which are unwanted also being highlighted.

"

LOAD*,
if(Comp1A <= Comp1B, 'Yes', 'No') as Flag_1;

LOAD
CompanyID,
[Year],
sum(if(PointID = 60684989045, VALUE, 0)) as Comp1A,
sum(if(PointID = 60684986288, VALUE, 0)) as Comp1B

Resident RAWDATA

WHERE EXISTS (PointID)
GROUP BY
Company,
[Year] "

[stevedark]

In what way?

Can you show a screenshot of a table where you have CompanyID, Year, Comp1A, Comp1B and Flag_1 and highlight where the result is wrong?

[harsha]

@stevedark ... results are coming but as i said, unnecessary fields are showing as YES 

i used this script from you.. green are correct fields to be highlighted..but i don't know why red records are coming with YES

LOAD *,
if(Comp1A <= Comp1B, 'Yes', 'No') as Flag_1,

;
LOAD
CompanyID,
[Year],
if(PointID = 60634843286, VALUE, 0) as Comp1A,
if(PointID = 60634843287, VALUE, 0) as Comp1B

Resident RAWDATA

WHERE EXISTS (PointID)

[stevedark]

I'm confused.

The query you are showing does not have VALUE or DataPointID or Test Series Year in it, so how are they showing in the table?

If you run DROP TABLE RAWDATA in the load script those extra fields will be cleared away and you can see a table just with the data that is calculated from the table above.

[harsha]

@stevedark ,

Steve, i just renamed the field names to make it more understandable in this community. Sorry if that created any confusion. I haven't dropped the RAWDATA.. may be that could be a prob. Let me drop it and try if that clears my issue.