Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ..!
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?
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
@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
@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