topic Re: Lookup Row Values into Columns dynamically in QlikView

Lookup Row Values into Columns dynamically

Anonymous — Fri, 18 Sep 2015 10:22:20 GMT

Dear Experts,

I would like to compare the row values with my column Headers dynamically.

Please refer the attachment.

First tab contains the Warehouse and stock details.I would like to compare the Postal Code which is dynamic in rows to compare the second tab data columns(WH1,2,3) which is static.

Example:

Material XXX12 is stocked in AAAB and AAAC and the postal codes are 16702 and 16703 respectively.I want to take 16702 and AAAB from 1st tab and go to Postcode details and compare WH1 which is not matching,then go to WH2.Here it matches.Then I would like to read my final output as WH2 i.e AAAB.

In some cases there won't be any match in the Postcode details.In such scenario I want to consider my Warehouse details code as my closest stocked ware house

Could you please help me in achieving this logic

Thanks & Regards

Jeba

Re: Lookup Row Values into Columns dynamically

Fri, 18 Sep 2015 11:58:00 GMT

Hi Jeba,

Here is a solution

WH_Raw:

CrossTable(WH, Value)

LOAD [Postal Code],

[WH 1],

[WH 2],

WH3

FROM

(ooxml, embedded labels, table is [Postcode details]);

WH:

Mapping

Load AutoNumber([Postal Code]&Value) as Key,

WH Resident WH_Raw;

DROP Table WH_Raw;

Stocked:

LOAD Material,

[Stocked wh],

[Postal Code],

Applymap('WH',AutoNumber([Postal Code]&[Stocked wh]),'No Matching WH') as WH

FROM

(ooxml, embedded labels, table is [Ware House Details]);

Re: Lookup Row Values into Columns dynamically

Anonymous — Fri, 18 Sep 2015 12:57:06 GMT

Dear Experts,

Can anyone show me the directions please.

Regards

Jeba

Re: Lookup Row Values into Columns dynamically

petter — Fri, 18 Sep 2015 14:14:49 GMT

I think this might be a solution for you - using a CROSSTABLE() LOAD to get your static Ware Houses from columns into rows and then connecting the two tables by creating a concatenated key of "Stocked wh" and "Postal Code" which will enable the right association:

Re: Lookup Row Values into Columns dynamically

Anonymous — Fri, 18 Sep 2015 15:04:43 GMT

Hi Petter,

Thanks for your guidance.

Since I have huge data it will affect my performance when I use Cross Table.However since there is no alternate solution I will use this.

One last thing.

When we don't have the match in the second table it should fetch only the first value of the first table.This is fetching all values.

Could you please guide me

Thanks & Regards

Jeba

Re: Lookup Row Values into Columns dynamically

petter — Fri, 18 Sep 2015 16:33:36 GMT

Then I think a MAPPING LOAD along with an ApplyMap() will do the trick for you. That way we get rid of the key and also get a default value if there is no match... not sure which one of the fields you meant as a default value but it is very easy to exchange it to want you want anyway in the ApplyMap()-function...

Re: Lookup Row Values into Columns dynamically

Anonymous — Tue, 22 Sep 2015 09:02:34 GMT

Hi Petter,

Sorry for the delay in responding.

I tested the logic and it worked fine.

Thanks a lot for your help

Thanks & Regards

Jeba