Mapping a Canadian Postal Code to a Postal Code Interval

[InsightlyStudio]

Hi there, 

I am new to Qlik Sense and so just getting familiar with how to perform basic functions. 

I am having trouble applying the IntervalMatch function to how I would have typically applied Xlookup functions in excel. I am trying to do lookups with Canadian Postal codes  format (letternumberletter numberletternumber, eg A0A 1A0)

I currently have the two tables:

//this is the range table where there is an additional charge for extended region if the postal code falls within the Postal Code From and Postal Code To interval with Charge being the fee. 

[Extended$]:
LOAD
[Postal Code From],
[Postal Code To],
[Charge]

// These are all the Canadian postal codes
[PostalCodeDatabase]:
LOAD
TRIM(REPLACE([PostalCode],' ','')) as [PostalCode],
[City],
[Province],
[AreaCode],
[Latitude],
[Longitude],
[CityMixedCase],
[RecordType],
GeoMakePoint([Latitude], [Longitude]) AS [Longitude_Latitude]

Note that not all PostalCodes in the PostalCodeDatabase will fall within a range on the Extended table. In that case the output would be $0 or "Not Found". Where a PostalCode fits within a range I'd like to map the Postal Code From (lower limit), Postal Code To (upper limit) and Charge to the PostalCodeDatabase or a bridge table. If there wasn't a match it can be "Not Found", "Not Found", 0. 

I can do this pretty easily with XLookups and If then statements in excel, but can't quite figure out how to get the Xlookup function replicated. 

Any help anyone can provide would be super appreciated!!

Thanks,
Julie

[InsightlyStudio]

@ArnadoSandoval  I was able to figure out the formula for converting characters to ASCII numbers (I believe). But I am still getting null values in the lookup. 

Below is my script:

NoConcatenate
PC_RangeRate:
LOAD
"Postal Code From",
"Postal Code To",
Charge,
(Ord("Postal Code From")&Ord(Right("Postal Code From",5))&Ord(Right("Postal Code From",4))&Ord(Right("Postal Code From",3))&Ord(Right("Postal Code From",2))&Ord(Right("Postal Code From",1)))/100000000 as PC_From_Hex,
(Ord("Postal Code To")&Ord(Right("Postal Code To",5))&Ord(Right("Postal Code To",4))&Ord(Right("Postal Code To",3))&Ord(Right("Postal Code To",2))&Ord(Right("Postal Code To",1)))/100000000 as PC_To_Hex

FROM [lib://DataFiles/UPS extended zone exercise .xls]
(biff, embedded labels, table is Extended$);

Load
Null() as "Postal Code From",
Null() as "Postal Code To",
0 As Charge,
Null() as PC_From_Hex,
Null() as PC_To_Hex
AutoGenerate (1);

Map_PC_RangeRate:
Mapping Load
"PC_From_Hex" & '|' & "PC_To_Hex",
Charge
Resident PC_RangeRate;

Map_PC_From:
Mapping Load
"PC_From_Hex" & '|' & "PC_To_Hex",
"Postal Code From"
Resident PC_RangeRate;

Map_PC_To:
Mapping Load
"PC_From_Hex" & '|' & "PC_To_Hex",
"Postal Code To"
Resident PC_RangeRate;

NoConcatenate

Temp_Table:
Load
"PostalCode" as PostalCodeReal,
"City",
"Province",
"AreaCode",
"Latitude",
"Longitude",
"CityMixedCase",
"RecordType",
GeoMakePoint("Latitude", "Longitude") AS "Longitude_Latitude",
(Ord("PostalCode")&Ord(Right("PostalCode",5))&Ord(Right("PostalCode",4))&Ord(Right("PostalCode",3))&Ord(Right("PostalCode",2))&Ord(Right("PostalCode",1)))/100000000 as "PostalCode"
FROM [lib://DataFiles/PostalCodeDatabase.xlsx]
(ooxml, embedded labels, table is PostalCodeDatabase);

Left Join
IntervalMatch (PostalCode)
Load "PC_From_Hex","PC_To_Hex"
Resident PC_RangeRate;

PostalCodeDatabase:
Load
PostalCode,
City,
Province,
AreaCode,
Latitude,
Longitude,
CityMixedCase,
RecordType,
ApplyMap('Map_PC_RangeRate', "PC_From_Hex" & '|' & "PC_To_Hex" ) as RangeCharge,
"PC_From_Hex" as PC_From,
"PC_To_Hex" as PC_To,
GeoMakePoint(Latitude, Longitude) AS Longitude_Latitude,
ApplyMap('Map_PC_From',"PC_From_Hex" & '|' & "PC_To_Hex") as PC_From_Real,
ApplyMap('Map_PC_To', "PC_From_Hex" & '|' & "PC_To_Hex") as PC_To_Real,
PostalCodeReal

Resident Temp_Table;
Drop Table Temp_Table;

Below is the screenshot of the result: 

Anything you can see wrong in the code? 

[ArnadoSandoval · Accepted solution]

Hi @InsightlyStudio 

You did a lot of typing while I was trying to resolve your issue integrating with R; I wrote the R function but I was having difficulties integrating the formula with the Qlik Script; I am glad you worked it out in the script; I changed the formula a little bit in two areas, first avoiding typing those long columns name, and second fine-tuning the formula.

I aliased three new columns within the script:

• "Postal Code From" As PCf
• "Postal Code To" As PCt
• "PostCode" As PC

The original columns remained as part of the solution while PCf, PCt and PC were used in the formula, with a lot of less typing; I did preceding load to introduce these columns.

The script for the PC_RangeRate looks like this:

PC_RangeRate:
Load 
    "Postal Code From",
    "Postal Code To",
    Charge,
    Num(PC_From_Hex) As PC_From_Hex_Old,
    Num(PC_To_Hex)   As PC_To_Hex_Old,
    Num(Text( Ord(Mid(PCf,1,1))*1000000 + Ord(Mid(PCf,2,1))*1000 + Ord(Mid(PCf,3,1)) ) &'.'&
    Text(100000000 + Ord(Mid(PCf,4,1))*1000000 + Ord(Mid(PCf,5,1))*1000 + Ord(Mid(PCf,6,1)))) as PC_From_Hex,
    Num(Text( Ord(Mid(PCt,1,1))*1000000 + Ord(Mid(PCt,2,1))*1000 + Ord(Mid(PCt,3,1)) ) &'.'&
    Text(100000000 + Ord(Mid(PCt,4,1))*1000000 + Ord(Mid(PCt,5,1))*1000 + Ord(Mid(PCt,6,1)))) as PC_To_Hex
;

Load *,
     "Postal Code From" As PCf,
     "Postal Code To"   As PCt
Resident PC_RangeRate_Temp;

The data was loaded to a temporary table: PC_RangeRate_Temp,  here the columns PCf and PCt where introduced, the formula looks a little bit different, but still using the Ord() function but the Right() function is not the correct one to use, I replaced it with the Mid() function because we get the ordinal from a particular position in the PostCode (actually, as I write my reply I realize it should work with the Right() function, but it is too late on my side, I already replaced it)

    Num(Text( Ord(Mid(PCf,1,1))*1000000 + Ord(Mid(PCf,2,1))*1000 + Ord(Mid(PCf,3,1)) ) &'.'&
    Text(100000000 + Ord(Mid(PCf,4,1))*1000000 + Ord(Mid(PCf,5,1))*1000 + Ord(Mid(PCf,6,1)))) as PC_From_Hex

You will notice few things here:

There are two Text() functions concatenated with a dot (.) between them, that is a decimal point.

The first 3 characters of the PostCode go in the first Text() functions, the remaining 3 on the second Text()

The Ord() number of the first character of the PostCode is multiplied by 1000000 (one million), so the letter 'A' become 65000000; the second character is multiplied by 1000 (one thousand) , so the letter '0' become 48000, when we add these results, we get 65048000, here the ordinal of the first character is not added to the ordinal of the second character, finally the last character is taken as it is, so for a letter 'A' is once again 65, adding these three ordinals: 65000000 + 48000 + 65 we get the number 65048065; the reason for using a thousand factor is because the ordinal for some characters are over one hundred causing overlapping additions. The sames logic was applied with the last 3 characters of the postcode, with the difference of adding an initial 100000000 (one hundred millions) the reason is once again preventing unexpected results if a post code contain lower case characters, as their ORD number is usually over 100.
With the PC_DataBase the column PC was introduced for the same reason, making easier to code the long function,  the remaining part of the script is here:

NoConcatenate 

Temp_Table:
Load
    PostCode_Real,
    City,
    Province,
    "Area Code",
    PostCode_Old,
    Num(Text( Ord(Mid(PC,1,1))*1000000 + Ord(Mid(PC,2,1))*1000 + Ord(Mid(PC,3,1)) ) &'.'&
    Text(100000000 + Ord(Mid(PC,4,1))*1000000 + Ord(Mid(PC,5,1))*1000 + Ord(Mid(PC,6,1)))) as PostCode,    
;

LOAD
    "PostCode" as PostCode_Real,
    "PostCode" as PC,
    City,
    Province,
    "Area Code",
    Num(PC_Hex) As PostCode_Old
FROM [lib://PostalCodes/PC_DataBase.txt]
(txt, codepage is 28592, embedded labels, delimiter is ',', msq);
Left Join 
IntervalMatch (PostCode) Load "PC_From_Hex", "PC_To_Hex" Resident PC_RangeRate; 

PC_DataBase:
Load PostCode,
     City,
     Province,
     "Area Code",
     ApplyMap('Map_PC_RangeRate', "PC_From_Hex" & '|' & "PC_To_Hex" ) as RangeCharge,
     "PC_From_Hex" as PC_From,
     "PC_To_Hex"   as PC_To,
     ApplyMap('Map_PC_From', "PC_From_Hex" & '|' & "PC_To_Hex" ) as PC_From_Real,
     ApplyMap('Map_PC_To', "PC_From_Hex" & '|' & "PC_To_Hex" ) as PC_To_Real,
     PostCode_Real
Resident Temp_Table;

Drop Table Temp_Table;
Drop Table PC_RangeRate_Temp;

Your formula is lovely, I am glad you figured it out!

Best regards,

[InsightlyStudio]

Thanks @ArnadoSandoval  let me give that a try! 

Was the source of the null issue that it wasn't taking account the potential 3 digits? or that it was still too long? 

[ArnadoSandoval]

@InsightlyStudio 

Exactly, you were pretty close, then I used TEXT function because Qlik is funny dealing with large numbers!

If you are happy with the code solving your issue just mark this thread as solved, so other people could find it helpful.

Regards,

[InsightlyStudio]

Darn - still getting a null result. 

I am going to step back and try loading a smaller data that is reconfigured to see if it is the formula or the actually mapping. 

[InsightlyStudio]

Oh - as I looked at the screenshot I realized my error. I had removed my Trim/Substitute space formula to simplify and so it wasn't matching because of the space.  I just modified the mid formula for 4,5,6 to 5,6,7 and it worked. 

Working now!!! 

Thanks so much for your help - you are a total rockstar.