topic Re: Table with 3 key fields to lookup tables in QlikView

Table with 3 key fields to lookup tables

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Table with 3 key fields to lookup tables

sunny_talwar — Fri, 22 Jul 2016 22:13:53 GMT

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

Re: Table with 3 key fields to lookup tables

damirm86 — Fri, 22 Jul 2016 22:15:42 GMT

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

Re: Table with 3 key fields to lookup tables

maxgro — Sat, 23 Jul 2016 01:44:17 GMT

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

Re: Table with 3 key fields to lookup tables

Anonymous — Mon, 25 Jul 2016 05:00:09 GMT

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!

Re: Table with 3 key fields to lookup tables

sunny_talwar — Mon, 25 Jul 2016 12:39:24 GMT

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

Re: Table with 3 key fields to lookup tables

Anonymous — Mon, 25 Jul 2016 14:35:43 GMT

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!