Help me to map values without duplicates.

[durgabhavani]

Hi All,

Please help me to map values to my field category with out duplicates in row. Please find expected output below. Already I have tried mapsubstring function but not able to remove duplicates field values in row.

Category
01,01
02,04
05,08
09,07,03
02,04,02,03
05,05,06,06
04,03,06
03,04,05,06
01,02,03,04

Mapping File data:   

Category_Level	Category_Name
01	Charity
02	Global
03	Cash
04	Other
05	Gaming
06	Business
07	Government
08	Hospital
09	Gold

Expected Output.

Category

Charity

Global,Other

Gaming,Hospital

Gold,Government,Cash

Global,Other,Cash

Gaming,Business

Other,Cash,Business

Cash,Other,Gaming,Business

Charity,Global,Cash,Other

Please advice.

Thanks,

Durga

[sunny_talwar · Accepted solution]

Try this

MappingTable:

Mapping

LOAD * INLINE [

    Category_Level, Category_Name

    01, Charity

    02, Global

    03, Cash

    04, Other

    05, Gaming

    06, Business

    07, Government

    08, Hospital

    09, Gold

];

FactTable:

LOAD Key,

MapSubString('MappingTable', Concat(DISTINCT Category, ', ')) as Category

Group By Key;

LOAD Category as Key,

SubField(Category, ',') as Category;

LOAD * INLINE [

    Category

    "01,01"

    "02,04"

    "05,08"

    "09,07,03"

    "02,04,02,03"

    "05,05,06,06"

    "04,03,06"

    "03,04,05,06"

    "01,02,03,04"

];

[durgabhavani]

Thank you sunny... Its working....