Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
leenlart
Creator
Creator

Mapping Load with a date condition added

Hello,

I have an application that loads monthly client sales of our products.  There is a mapping table with the correspondance client_itemcode --> internal_itemcode.  

 

Spoiler

mapping LOAD * Inline [

client_itemcode , internal_itemcode

A, X

B, W

C, U...

The invoices are loaded and mapped to the internal_code to then link to our product hierarchy and to compare with our out-going sales.

Recently the team informed me that the client has changed a few items.  Example they are now purchasing our internal_itemcode Y and selling it as client_itemcode A, where before our internal_itemcode X was sold  as client_itemcode A.  

For historic views, we should keep the old mapping.  So my question is what is the best way to add a date as a condition of the mapping ?  I imagine the date field in the mapping to be the first date of that mapping combination.  

Currently my mapping commande in the invoices load script looks like :

text(Applymap('Correspondance_LTR_to_VIF', client_itemcode, null())) as %ARTICLE_ID,

Thanks for your help!

Labels (3)
1 Solution

Accepted Solutions
leenlart
Creator
Creator
Author

4 Replies
Oleg_Troyansky
Luminary Alumni
Luminary Alumni

Hi,

this is a tricky problem. If you added a date field into your mapping table, you'd have to repeat the same item codes for every single date in the past and for every single date in the future (e.g. after the change). Plus, the changes could continue in the future...

I'd introduce another code, like "Version", and calculate a secondary mapping table that would be built using IF() conditions, with conditions like "For Item A and dates before X, use Version 1, for Item A and dates after X, use version 2. Then, the main mapping table could be enhanced with the "Version" field - for example, the first field could be concatenated between the Client Item Code and the Version field. 

Cheers,

leenlart
Creator
Creator
Author

Hi Oleg, 

Thanks for your reply.  

I forgot that a mapping table have only two columns!

I like the idea of your solution, however it limits the number of versions to the number of secondary tables I want to write in.  Potentially, the code could change multiple time through the years.  Right now, I only have one change for a handful of codes, but there's no reason that codes could not change again in the next financial year, etc.  

Maybe a mapping load is not the best solution.  My problem rests in the version date field for the correspondance between the client and internal codes.  Maybe I need to do some sort of join between the two tables (invoices and client/internal code correspondance) that would allow me to do a conditional join on the date version compared to the invoice date. 

 What do you think ?

Regards,

Kathleen

leenlart
Creator
Creator
Author

Oleg_Troyansky
Luminary Alumni
Luminary Alumni

Hi Kathleen,

I think that you could define your code changes in such a way that won't require constant changes in the script. On the other hand, using multiple JOIN statements with WHERE conditions would actually require to add another join every time there is a change in codes.

Cheers,