topic Re: Join without prefix in App Development

Join without prefix

Anonymous — Fri, 24 Nov 2017 16:52:10 GMT

I have a Customer Table and an Order Table. Both tables have a matching Order field however the Order field in the Customer Table has a 3 string character prefix whereas the Order field in the Order Table does not. What is the best way to join these tables if I want to remove the 3 character prefix from the Order field in the Customer Table?

Customer Table
OrderID	CategoryName
adr-10413	Men´s Clothes
gty-10500	Womens wear
lok-10832	Womens wear
poi-10923	Womens wear
mnb-10413	Womens wear
vfg-10425	Womens wear
seq-10832	Sportwear
zxc-10425	Sportwear
cpw-10413	Sportwear
ihb-10923	Bath Clothes
wjh-10832	Ladies´Footwear
itn-10923	Ladies´Footwear
zxw-10500	Children´s wear
dac-10371	Babywear
iuy-10832	Babywear
orn-10610	Babywear
mne-10806	Men´s Clothes
alh-10850	Men´s Clothes

Order Table
OrderID	Sales
10425	$9,035.75
10413	$5,809.44
10923	$1,842.34
10832	$1,308.29
10413	$1,214.80
10850	$927.71
10413	$504.24
10806	$476.50
10425	$450.50
10500	$394.13
10610	$198.98
10832	$165.60
10923	$158.40
10923	$154.08
10832	$99.18
10832	$92.16
10500	$81.02
10371	$55.94

Re: Join without prefix

Anonymous — Fri, 24 Nov 2017 19:59:59 GMT

Try using the subfield() command, here is an example that returns just 10413

SubField('adr-10413', '-' , 2 )

It is described in the Help String functions ‒ Qlik Sense

Re: Join without prefix

Anonymous — Sun, 10 Dec 2017 22:39:33 GMT

Thank you. Can you show me where to insert "SubField('adr-10413', '-' , 2 )" in the script below from the Data Load Editor?

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET CreateSearchIndexOnReload=1;

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LOAD

OrderID,

CategoryName

FROM [lib://Qlik Posts/Orders.xlsx]

(ooxml, embedded labels, table is [OrderID-Category]);

LOAD

OrderID,

Sales

FROM [lib://Qlik Posts/Orders.xlsx]

(ooxml, embedded labels, table is [OrderID-Sales]);

Re: Join without prefix

Anonymous — Tue, 12 Dec 2017 05:22:24 GMT

You can use Bill's expression in [OrderID-Category] table as

Subfiled(OrderID,'-',2) as NewID --- alias

Re: Join without prefix

pradosh_thakur — Tue, 12 Dec 2017 06:27:01 GMT

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET CreateSearchIndexOnReload=1;

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LOAD

OrderID as old_OrderID,

Subfiled(OrderID,'-',2) as OrderID,

CategoryName

FROM [lib://Qlik Posts/Orders.xlsx]

(ooxml, embedded labels, table is [OrderID-Category]);

LOAD

OrderID,

Sales

FROM [lib://Qlik Posts/Orders.xlsx]

(ooxml, embedded labels, table is [OrderID-Sales]);

Regards

Pradosh