Qlik Sense - Joining 2 tables, using an expression

[#1]

Hello,

I would very much appreciate you help in solving this case:

I have these 2 tables which I would like to join, using a "wild" match between the Location (e.g. FR-PARIS) and the Expression (e.g. FR-*) so that I can associate the Group to the Order (e.g. FRANCE) :

Order:

ORDERID	Amount	Location
1	50	FR-PARIS
2	20	FR-NICE
3	12	IT-ROME
4	56	IT-MILAN

Grouping:

Group	Expression
FRANCE	FR-*
ITALY	IT-*

I would like to obtain this table at the end:

OrderWithGrouping:

ORDERID	Amount	Location	Group
1	50	FR-PARIS	FRANCE
2	20	FR-NICE	FRANCE
3	12	IT-ROME	ITALY
4	56	IT-MILAN	ITALY

I am creating the 2 first tables above this way:

Order:

Load * inline

[ORDERID, Location,  Amount

1,       FR-NICE,   200      

2,       FR-PARIS,  100

3,       IT-ROME,   300

4,       IT-VENISE, 400

];

Grouping:

Load * inline

[Group,  Expression

FRANCE, FR-*

ITALY,  IT-*

];

I don't know how to join these 2 tables in order to join them and obtain the above table OrderWithGrouping

It would be very helpful if you could give me some advice. Many thanks

Annick

[Gysbert_Wassenaar]

Perhaps like this:

mapCountry:

LOAD * INLINE [

Substring, Country

FR, FRANCE

IT, ITALY

];

Order:

LOAD

     *,

     applymap('mapCountry', subfield(Location, '-',1)) as Country

FROM

     ...

     ;

[Michael_Tarallo]

Hi Annick,

Take a look at this as well.

• Qlik Sense - Dimension Grouping (video)

let us know ho you do

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

[#4]

Hello Gysbert,

Thanks a lot for your reply. Indeed your answer is correct and works for the particular scenario that I have described. However I should have mentioned that I would like to support different types of expressions.

For example "FR-*" and "**-MARSEILLE".

I could have :

Order:

ORDERID	Amount	Location
1	50	FR-PARIS
2	20	FR-NICE
3	12	IT-ROME
4	56	IT-MILAN
5	34	XX-MARSEILLE

Grouping:

Group	Expression
FRANCE	FR-*
ITALY	IT-*
MRS	**-MARSEILLE

I would like to obtain this table at the end:

OrderWithGrouping:

ORDERID	Amount	Location	Group
1	50	FR-PARIS	FRANCE
2	20	FR-NICE	FRANCE
3	12	IT-ROME	ITALY
4	56	IT-MILAN	ITALY
5	34	XX-MARSEILLE	MRS

Thanks

Kind regards

[#5]

Hello Michael,

I have added some precisions to my initial request. I will check the video and see if it helps !

Thanks a lot.

Annick

[Gysbert_Wassenaar]

Ok, try this then:

mapGroup:

LOAD * INLINE [

Substring, Group

FR, FRANCE

IT, ITALY

MARSEILLE, MRS

];

Order:

LOAD

     *,

     applymap('mapGroup', subfield(Location, '-',1), applymap('mapGroup', subfield(Location, '-',-1) ) as Group

FROM

     ...

     ;

[martin_dideriks · Accepted solution]

Have a look at the example from Rob Wunderlich:Mapping with wildcards.

http://qlikviewcookbook.com/download/mapping-with-wildcards/

[#8]

Thanks a lot! It does work. However for now, I think I will implement the formula provided below by Martin as it is more generic and my expression might evolve. Thanks a lot for your time. Annick