Model suggestion

[Krish2459_58]

Hi,

I have attached an excel with problem scenario and required output. Please have alook.

Thanks....

[Krish2459_58]

Hi, Can I have suggestions to deal with the scenario posted in the attached sample excel(Book2).

[seanbruton · Accepted solution]

Please try this solution.....

TBL_A:

LOAD * INLINE [

Product_Ref|Product_category
P1|Head
P2|Head
P3|Head
P4|Head
P5|Head
P6| Head
PS7|Sub Head
PS8|Sub Head
PS9|Sub Head
PS10|Sub Head
] (DELIMITER IS '|');

TBL_B:
LOAD * INLINE [

Product_RefHead|Product_Ref_SubHead|Unit
P1||Hygiene
P2||Doors
P3|PS7|Programmed Waste Disposal or Recycling
P4|PS8|Carpentry
P5|PS9|Programmed Recycling
P6|PS10|Programmed Water Cooler Maintenance
] (DELIMITER IS '|');

TBL_B_1:

LOAD Product_RefHead&'|'&Product_Ref_SubHead AS Key_1, * RESIDENT TBL_B;
DROP TABLE TBL_B;

//EXIT SCRIPT;
TBL_A_1:

LOAD Product_Ref AS Product_Ref_, Product_category as Product_category_
RESIDENT TBL_A WHERE Product_category = 'Head';

//EXIT SCRIPT;
LEFT JOIN (TBL_A_1)

LOAD Product_Ref AS Product_Ref_SUB
RESIDENT TBL_A WHERE Product_category = 'Sub Head';

TBL_A_2:

LOAD * WHERE EXISTS(Key_1);

LOAD Product_Ref_&'|'&Product_Ref_SUB AS Key_1,* RESIDENT TBL_A_1;

DROP TABLES TBL_A_1,TBL_A;

Your date model with look like this:

The table of sheet has these results:

Regards