Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
valpassos
Creator II
Creator II

Just one small thing on mapping table use

Hi community,

This will be for an easy and quick wedge.

A mapping table always returns one and one only value, right.

MAPPING LOAD
%KEY_PRODUCT,
MIN(Order_Date)
FROM Table
GROUP BY %KEY_PRODUCT;

So, this mapping table will return the MIN(Order_Date) for the product.

But if I have multiple Order_Dates, varying for each version of the product, a mapping table no longer makes sense to use, right? Even if I GROUP BY the version of the product?

Is this true under any circunstance? 

If so, what should I do, then - a LEFT JOIN?

Thanks!

Lisa

Labels (1)
1 Solution

Accepted Solutions
pablolabbe
Luminary Alumni
Luminary Alumni

You have two options:

Use a left join using %KEY_PRODUCT and VERSION (field with version of the product) as matching fields.

Or you can create a mapping table concatenating product key and product version fields in one single field and use it as the mapping column:

 

MAPPING LOAD
%KEY_PRODUCT&'|'&VERSION,
MIN(Order_Date)
FROM Table
GROUP BY %KEY_PRODUCT,VERSION;

 

View solution in original post

5 Replies
Wederson
Partner
Partner

The mapping table returns one value for each key value. In yout Example, the mapply will return the minium order_date for each KEY_PRODUCT.

pablolabbe
Luminary Alumni
Luminary Alumni

You have two options:

Use a left join using %KEY_PRODUCT and VERSION (field with version of the product) as matching fields.

Or you can create a mapping table concatenating product key and product version fields in one single field and use it as the mapping column:

 

MAPPING LOAD
%KEY_PRODUCT&'|'&VERSION,
MIN(Order_Date)
FROM Table
GROUP BY %KEY_PRODUCT,VERSION;

 

View solution in original post

valpassos
Creator II
Creator II
Author

Hi @pablolabbe ,

Thank you!

Immediately after I posted the question I realized that using a composite key, as you describe, on the mapping table maybe could also make sense 🤔 And it sure beats the LEFT JOIN in terms of performance, right?

I haven't acted upon that approach yet on my model, but using this composite key, can the Version field then be used as a filter on the front-end, so I can vary the order date?

Thanks once again!,

Lisa

pablolabbe
Luminary Alumni
Luminary Alumni

Hi Lisa,

  I'm not sure on what you are saying  "so I can so I can vary the order date?" . It depends on your data model. The Version field must exists in the table which you are applying the applymap function.

 

valpassos
Creator II
Creator II
Author

Hi @pablolabbe,

Forget about it -- that question didn't make sense 😊

Thanks!