Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
Have you tried adding TerminationCategory2 and TerminationCategory3 to TerminationCategory with the applymap function?
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
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!
Can you share a screenshot of the error you have received?
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!