topic Re: Qlik Sense - Joining 2 tables, using an expression in Visualization and Usability

Qlik Sense - Joining 2 tables, using an expression

Wed, 27 Apr 2016 12:29:22 GMT

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

Re: Qlik Sense - Joining 2 tables, using an expression

Gysbert_Wassenaar — Wed, 27 Apr 2016 13:28:16 GMT

Perhaps like this:

mapCountry:

LOAD * INLINE [

Substring, Country

FR, FRANCE

IT, ITALY

];

Order:

LOAD

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

FROM

...

;

Re: Qlik Sense - Joining 2 tables, using an expression

Michael_Tarallo — Wed, 27 Apr 2016 15:41:09 GMT

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

Re: Qlik Sense - Joining 2 tables, using an expression

Wed, 27 Apr 2016 16:26:29 GMT

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

Re: Qlik Sense - Joining 2 tables, using an expression

Wed, 27 Apr 2016 16:27:45 GMT

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

Re: Qlik Sense - Joining 2 tables, using an expression

Gysbert_Wassenaar — Wed, 27 Apr 2016 16:54:08 GMT

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

...

;

Re: Qlik Sense - Joining 2 tables, using an expression

martin_dideriks — Thu, 28 Apr 2016 06:02:29 GMT

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

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

Re: Qlik Sense - Joining 2 tables, using an expression

Thu, 28 Apr 2016 10:07:49 GMT

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