Load script help (lookup)

[tamilarasu]

Hello,

I am trying to do a lookup function to get a value and need to have it in a new column. I have concatenated column and need to get price based on highest position number. See below for example.

If there is only one unique value in concatenated column (no need to check position column), then the price should be displayed as it is column output. But if there are multiple values (nneed to check the position column) and I need to show the value which has highest position number.

In the above screenshot, I have multiple values from 16th to 23 (Need to check the position column) and I need to show the output as 945.70 (highest position number 80).

I have attached excel file. Please guide.

[jagan · Accepted solution]

Hi,

Try this script

Data:

LOAD

  RowNo() AS RowNo,

  Year,

     Month,

     Sold,

     [Prod Number],

     Currency,

     [Order Number],

     Concate,

     Pos,

     price

FROM

[DB.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD

  Concate,

     Max(RowNo) AS RowNo

RESIDENT Data

GROUP BY Concate;

LEFT JOIN(Temp)

LOAD

  Concate,

  RowNo,   

     price AS New_Price

RESIDENT Data;

LEFT JOIN (Data)

LOAD

Concate,

New_Price

RESIDENT Temp;

DROP TABLE Temp;

Regards,

Jagan.

[#3]

Try this. Thoroughly unit test it.

Temp:

LOAD Year,

     Month,

     Sold,

     [Prod Number],

     Currency,

     [Order Number],

     Concate,

     Pos,

     price

FROM

DB.xlsx

(ooxml, embedded labels, table is Sheet1);

DIS_CNT:

LOAD

Concate,

Max(Pos) as Pos

Resident Temp Group By Concate;

Inner Join

LOAD

Concate,

Pos,

price

Resident Temp;

Tab1:

NoConcatenate

LOAD

*

Resident Temp;

Inner Join

LOAD

Concate,

price as [Output needed]

Resident DIS_CNT;

DROP Table Temp;

DROP Table DIS_CNT;

Regards,

KKR

[Anonymous]

PFA

[maxgro]

another one

b:

LOAD *

FROM DB.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join (b)

load

  Concate,

  FirstSortedValue(price, -Pos) as Output

Resident b

group by Concate;

[#6]

Hi,

Please find the attached QVW.

[jagan]

Hi,

FirstSortedValue() will return Null value, if you have multiple values matching, so try to avoid using FirstSortedValue().  For this data this work, may be in realtime data this may fail.

Regards,

Jagan.