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]

Yes..i used the exact script... 

[sunny_talwar]

That is strange, because SNo is created in tab2... is it error-ing out while creating tab2 or while doing left join? it is difficult to know from your screen shot

[harsha]

@sunny_talwar , it was created but no results are generated from the flag.

[Kushal_Chawda]

@harsha  What will be the output for below data where you have multiple Point IDs for same period?

[sunny_talwar]

@harsha - Didn't you say the script was throwing an error message? is that resolved now? How did you resolve that? I think to help you with this, it might be helpful to know the exact script you are running so that we can see what might be wrong here.

[harsha]

@sunny_talwar , I might have changed after taking from here to qlik .. i don't really recall why it was caused... 
however, i copied the script again here and used the same in next time. It went well and no errors.

[sunny_talwar]

@harsha - can you share the exact script you are running?

[harsha]

@sunny_talwar , pls help me in resolving the issue as well.

tab2:
LOAD CompanyID,
[Year],
PointID,
VALUE,
If(CompanyID = Previous(CompanyID) and [Time Series Year] = Previous([Year]) and PointID = Peek('PointID') + 1, If(VALUE < Peek(VALUE), 'Yes', 'No'), '') AS tmpFlag,
If(CompanyID = Previous(CompanyID) and [Time Series Year] = Previous([Year]) and PointID = Peek('PointID') + 1, Peek('SNo'), RangeSum(Peek('SNo'), 1)) as SNo
Resident RAWDATA
WHERE MATCH(PointID, 60684989045, 60684986288)
Order By CompanyID, [TYear], PointID;

Left Join(tab2)
LOAD CompanyID,
[Year],
SNo,
MaxString(tmpFlag) as Flag_1
Resident tab2

Group By CompanyID, [Year], SNo;

DROP Field tmpFlag;

[Kushal_Chawda]

@harsha  try below

Data:
LOAD CompanyID, 
     PointID, 
     Year, 
     Value
FROM
[SampleData.xlsx]
(ooxml, embedded labels, table is Sheet1);

T1:
LOAD *,   if(rowno()=1,1,if(Mod(RowNo(),2)=1,Peek('Point_ID_Group')+1,Peek('Point_ID_Group'))) as Point_ID_Group
Resident Data
Order by CompanyID,Year,PointID;
 
DROP Table Data;

T2:
LOAD *,
      if(CompanyID=Peek(CompanyID) and Year =Peek(Year) and Point_ID_Group=Peek(Point_ID_Group) and  Value > Peek(Value),'Yes') as Flag1
Resident T1;

DROP Table T1;

Left Join(T2)
LOAD CompanyID, 
     Point_ID_Group, 
     Year,
     MaxString(Flag1) as Flag2
Resident T2
Group by CompanyID, 
     Point_ID_Group, 
     Year;

T3:
NoConcatenate
LOAD *,
     if(len(trim(Flag2))=0,'No',Flag2) as Flag
Resident T2;

DROP Table T2;

DROP Fields Flag1,Flag2,Point_ID_Group;

[sunny_talwar]

@harsha  I guess your Point ID are not consecutive... try this may be

tab2:
LOAD CompanyID,
    [Year],
    PointID,
    VALUE,
    If(CompanyID = Previous(CompanyID) and [Time Series Year] = Previous([Year]), If(VALUE < Peek(VALUE), 'Yes', 'No'), '') AS tmpFlag,
    If(CompanyID = Previous(CompanyID) and [Time Series Year] = Previous([Year]), Peek('SNo'), RangeSum(Peek('SNo'), 1)) as SNo
Resident RAWDATA
WHERE MATCH(PointID, 60684989045, 60684986288)
Order By CompanyID, [TYear], PointID;

Left Join(tab2)
LOAD CompanyID,
    [Year],
    SNo,
    MaxString(tmpFlag) as Flag_1
Resident tab2

Group By CompanyID, [Year], SNo;

DROP Field tmpFlag;

Also, what is the difference between TYear and Year and Time Series Year?