Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
patricesalem
Creator II
Creator II

Load script, mapping to allocate sales sector based on post code range

Hello

I have two tables, one containing sales per postcode and another containing sector ranges allocated to salespeople :

SALES:

patricesalem_0-1655371006841.png

ZIP_2_SECTOR:

patricesalem_1-1655371045996.png

 

In my load script, I would like to add the sector to SALES table based on the range to which is linked the sales ZIP code (and country code also).
A simple join will not work for sure. So I was thinking about mapping the ZIP_2_SECTOR table to use it in the load statement of my SALES table. I don't think it will work neither.

Any tip, suggestion on the best way to go would be helpful.

Thanks
Pat

Labels (4)
6 Replies
mohitp
Contributor II
Contributor II

Hi,

Please append the below code after you load both the tables:

Inner Join IntervalMatch ( ZIP )
LOAD ZIP_START, ZIP_END
Resident ZIP_2_SECTOR;

patricesalem
Creator II
Creator II
Author

Great ! That looks perfect. 

Since I m not sure two countries can have (or not the same zip code), how can I also join at the same time on Country ?
Thanks
eddie_wagt
Partner - Creator III
Partner - Creator III

Hello @patricesalem ,

You can add Country as a keyfield.

 

ZIP_2_SECTOR:
NoConcatenate
LOAD * INLINE [ZIP_START, ZIP_END, SALESPERSON, COUNTRY, SECTOR
01000, 09999, XXXX, DE, 23
10000, 25999, YYYY,DE,25];

SALES:
NoConcatenate
LOAD * INLINE [CUSTOMER, ZIP, COUNTRY, SECTOR, QTY_SOLD
Blabla, 0500, DE, ?, 10
BliBliBli, 12500, DE,?,20
Blobloblo, 12500, NL,?,30
BluBluBlu, 12500, FR,?,5
];

inner Join (SALES)
IntervalMatch ( ZIP,COUNTRY )
LOAD ZIP_START as ZIP_START_TMP
, 	 ZIP_END   as ZIP_END_TMP
, 	COUNTRY 
Resident ZIP_2_SECTOR;

drop table ZIP_2_SECTOR;

 

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...

 

Kind regards

Eddie

If this answers your question or solves your issue, be sure to mark the answer as correct by clicking 'Accept as Solution'. This will mark the post as solved and other Qlikkies will gravitate towards this post as it as a possible solution for their issue. Multiple responses can be accepted as a solution so make sure to select all that apply.
patricesalem
Creator II
Creator II
Author

Hi Eddie

I'm testing your solution and it does not work :

patricesalem_0-1655412435565.png

 

Why dropping the ZIP_2_SECTOR ? If I drop it, I can't retrieve the salesman sector and name

thanks

patricesalem
Creator II
Creator II
Author

I found another way to do it thanks to another post :

COMPANY:

LOAD *,country as country_code;

load * inline

[

zip, company, country

500,Blabla,DE,
12500,BliBliBli,DE
12500,Blobloblo,NL
12500,BluBluBlu,FR

];

ZIP_2_ZONE:

load * inline

[

min, max, salesman, country_code
0100, 09999, TATA from DE, DE
10000, 25999, TOTO from DE,DE
10000, 25999, TATA from NL,NL
10000, 25999, TITI from FR,FR
];

Inner Join

Data:

IntervalMatch (zip, country_code) load num(min), num(max), country_code resident ZIP_2_ZONE;

Join(COMPANY)

LOAD * Resident ZIP_2_ZONE;

DROP Table ZIP_2_ZONE;

DROP Field country;
/*

 

what do you think against your method ?

eddie_wagt
Partner - Creator III
Partner - Creator III

Hello @patricesalem ,

Glad you found the solution that works. I think it is great for the requirements you specified. I did not know all the requirements  and  hence this is good. It all depends which match you want to make and which fields you want to keep. You could keep the ZIP_2_ZONE table if the users also need these dimensions (to know which countries are also in the data), but from the information I got you did not need it.

Keep up the good work! 

Regards

Eddie