4 Replies Latest reply: Dec 12, 2017 1:27 AM by pradosh thakur RSS

    Join without prefix

    Max Winston

      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
          Bill Markham

          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
            Max Winston

            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
                aparna v

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

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

                • Re: Join without prefix
                  pradosh thakur

                  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