topic Re: Load script help (lookup) in QlikView

Load script help (lookup)

tamilarasu — Thu, 09 Jul 2015 04:01:07 GMT

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.

Re: Load script help (lookup)

jagan — Thu, 09 Jul 2015 04:41:24 GMT

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.

Re: Load script help (lookup)

Thu, 09 Jul 2015 04:43:31 GMT

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

Re: Load script help (lookup)

Anonymous — Thu, 09 Jul 2015 04:48:27 GMT

PFA

Re: Load script help (lookup)

maxgro — Thu, 09 Jul 2015 04:56:30 GMT

another one

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;

Re: Load script help (lookup)

Thu, 09 Jul 2015 05:06:22 GMT

Hi,

Please find the attached QVW.

Re: Load script help (lookup)

jagan — Thu, 09 Jul 2015 05:34:07 GMT

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.