Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
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]);
You can use Bill's expression in [OrderID-Category] table as
Subfiled(OrderID,'-',2) as NewID --- alias
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