Lookup Row Values into Columns dynamically

[Anonymous]

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

[#2]

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]);

[Anonymous]

Dear Experts,

Can anyone show me the directions please.

Regards

Jeba

[petter]

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:

[Anonymous]

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

[petter · Accepted solution]

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...

[Anonymous]

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