Associate 2 tables with wildcard in Qliksense

[lilychau]

Hi all,

I am trying to associate 2 tables(table 1 and table 2) in Qlik sense, such that the result can look like table 3. May I ask if there is any suggestion to do so? 

Using Field 1 as the key, table 2 is associated with table 1 default colour (Red for A, Orange for B). For row with field 1 and year-month (e.g. B, 201805) matched, then it is associate with the specified colour, Green, instead of the default colour, Orange.

Table 1:

Field 1	Year-month	Colour
A	*	Red
B	*	Orange
B	201805	Green

Table 2:

Field 1	Year-month
A	201801
A	201802
B	201803
B	201805

Table 3:

Field 1	Year-month	Colour
A	201801	Red
A	201802	Red
B	201803	Orange
B	201805	Green

Thank you very much.

[Digvijay_Singh]

Could be multiple ways to achieve this, may be one of the way like this - 

Table1:
Load * inline [
Field1 Year-month Colour
A * Red
B * Orange
B 201805 Green
](delimiter is spaces)
;

color_map:
Mapping Load
Field1,Colour
resident Table1
Where "Year-month" = '*'
;

Table2:
Load *
inline [
Field1 Year-month
A 201801
A 201802
B 201803
B 201805
](delimiter is spaces)
;

Left Join(Table2)
Load *
resident Table1
;

Drop table Table1
;

Final:
Load *,
Coalesce(Colour,ApplyMap('color_map',Field1)) as ColourNew
resident Table2;

Drop Field Colour;
Drop Table Table2;

[lilychau]

Thank you for answering, for my case, table 2 is in QVS format (with millions rows of data), and table 1 is excel with thousands of rows. May I ask if the one you suggested still applicable to it?

[Vegar]

You could look at Rob Wunderlich sample for wildchar Mapping, "Mapping with a table using wildcards".

https://qlikviewcookbook.com/list-recipes/#squelch-taas-accordion-shortcode-content-13

Or examine the and use the Qlikview Components (http://qlikviewcomponents.org) function "Qvc.CreateWildMapExpression".