topic Re: Associate 2 tables with wildcard in Qliksense in Connectivity & Data Prep

Associate 2 tables with wildcard in Qliksense

lilychau — Fri, 15 Oct 2021 02:11:51 GMT

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.

Re: Associate 2 tables with wildcard in Qliksense

Digvijay_Singh — Fri, 15 Oct 2021 02:38:13 GMT

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;

Re: Associate 2 tables with wildcard in Qliksense

lilychau — Fri, 15 Oct 2021 04:26:46 GMT

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?

Re: Associate 2 tables with wildcard in Qliksense

Vegar — Fri, 15 Oct 2021 04:58:45 GMT

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".