Compare two itemIDs' values and highlight with flag

[harsha]

Hi Experts,

Hope everyone is doing great..!

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 applied below script to get anomalies and It was working awesome with 9 million data.

"

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;

"

I received a new QVD file from other team to find the discrepancies today with 26 million data and I have applied the same solution but this time it is 3 hours still the data is in loading stage. I tried making some changes to see if the loading gets faster but no use.  

Request you to please help ..!

[harsha]

Hi @Kushal_Chawda ,

i have hundreds of PointIDs and i want to apply based on rules like (compare PointID 123<PointID 215, as Flag_1) and (PointID 523<PointID 982, as Flag_2).

so, the above code works only if we want to apply on all point IDs which are consecutive... is it so? 

[harsha]

Hi @sunny_talwar , sorry for the confusion here. Year is the right field. pls ignore Year and Time Series Year.

could you share me your whatsapp number pls? i really stuck at a stage and i have a presentation really very soon. really helpful if you can 

[Kushal_Chawda]

@harsha yes it will work for all PointIDs. assuming that they are continuous. For eg. there should not be any point id which is there in ome year but next point id fall in another year. if there is different logic to handle it let me know

[harsha]

@Kushal_Chawda , yes kush. PointIDs are not continuous. 

logic is 

I want to compare the value of pointID (60634843286) with other value of pointID (60634843285)

Ex:

CompanyID: 4129034, year: 2017, value of pointID: 1574 is 28790 greater than the 
CompanyID: 4129034, year: 2017, value of pointID: 3285 is 14570