Table with 3 key fields to lookup tables

[Anonymous]

Hello,

I have two tables: Termination and TerminationCategory.  Termination keeps track of an individuals' term record and all the facts about what occurred (PersonGUID, EffectiveDate, etc) and Termination Category is simply a list of the various categories (TerminationCategory, TerminationCategoryDescription).

Within termination, we are able to set three separate categories for the record and each category uses the same lookup table to look at the title of the category for each person.  In SQL, I can do this with an aliased table and join it back to Termination.  I can't figure out how to link all three term category fields to the same dimension table to find the title of the category without creating synthetic keys.

Termination:

LOAD  EffectiveDate,

    PersonGUID,

    TerminationCategory,

    TerminationCategory2,

    TerminationCategory3,

    TerminationTypeCode

FROM [..\QVD\1_Termination.qvd] (qvd);

TerminationCategory:

LOAD TerminationCategory,

     TerminationCategoryDescription

FROM [..\QVD\1_TerminationCategory.qvd] (qvd);

Thank you

[sunny_talwar · Accepted solution]

Why don't you do this:

TerminationCategoryMapping:

Mapping

LOAD TerminationCategory,

    TerminationCategoryDescription

FROM [..\QVD\1_TerminationCategory.qvd] (qvd);

Termination:

LOAD  EffectiveDate,

    PersonGUID,

    TerminationCategory,

    TerminationCategory2,

    TerminationCategory3,

    ApplyMap('TerminationCategoryMapping', TerminationCategory) as TerminationCategoryDescription,

    ApplyMap('TerminationCategoryMapping', TerminationCategory2) as TerminationCategoryDescription2,

    ApplyMap('TerminationCategoryMapping', TerminationCategory3) as TerminationCategoryDescription3,

    TerminationTypeCode

FROM [..\QVD\1_Termination.qvd] (qvd);

[damirm86]

Have you tried adding TerminationCategory2 and TerminationCategory3 to TerminationCategory with the applymap function?

[maxgro]

In SQL, I can do this with an aliased table and join it back to Termination.  I can't figure out how to link all three term category fields to the same dimension table to find the title of the category without creating synthetic keys.

If you want to do this in Qlik as in SQL you can load many times the same table, TerminationCategory, renaming the table and the fields (like 3 view in SQL):

Termination:

LOAD  EffectiveDate,

    PersonGUID,

    TerminationCategory,

    TerminationCategory2,

    TerminationCategory3,

    TerminationTypeCode

FROM [..\QVD\1_Termination.qvd] (qvd);

TerminationCategory:

LOAD TerminationCategory,

    TerminationCategoryDescription

FROM [..\QVD\1_TerminationCategory.qvd] (qvd);

TerminationCategory2:

LOAD TerminationCategory as TerminationCategory2,

    TerminationCategoryDescription as TerminationCategoryDescription2

FROM [..\QVD\1_TerminationCategory.qvd] (qvd);

TerminationCategory3:

LOAD ...........

For small decod tables as your Termination Category, 2 fields, 1 code and 1 description, I usually go for  Sunny's solution

[Anonymous]

Termination:

LOAD  EffectiveDate,

    PersonGUID,

    ApplyMap('TerminationCategoryMapping', TerminationCategory, Null()) as TerminationCategoryDescription,

    ApplyMap('TerminationCategoryMapping', TerminationCategory2, Null()) as TerminationCategoryDescription2,

    ApplyMap('TerminationCategoryMapping', TerminationCategory3, Null()) as TerminationCategoryDescription3,

    TerminationTypeCode

FROM [..\QVD\1_Termination.qvd] (qvd);

TerminationCategoryMapping:

Mapping

LOAD TerminationCategory,

    TerminationCategoryDescription

FROM [..\QVD\1_TerminationCategory.qvd] (qvd);

Thank you for the direction, I have never used the mapping feature before as our data only has one or two instances where I know this will be used.  When I tried using your exact script I received an error saying there was a missing piece of the function.  After opening the document for the first time since Friday, I removed the ', Null()' part and the error did not appear - so not really sure what the issue was last week.

Much appreciated!

[sunny_talwar]

Can you share a screenshot of the error you have received?

[Anonymous]

Sunny,

I received the "Mapping requires 2-column input" error and a quick google recommended adding the 'Null()' part.  Not sure why the error went away though.

Thank you!