4 Replies Latest reply: Nov 7, 2017 5:41 AM by Stephen Hasson RSS

    Setting up a new data field based on an database field being read

    Stephen Hasson

      Hi,

       

      I have a numeric value (a number of days related to late delivery) being read in from our ERP database and would like a new field in Qlik to be available, set based on this value.

       

      Using load editor, can i setup a 'category' value using logic like this?

       

      If no-of-days-late <= 7 then category = '1' Else

         If no-of-days-late >= 8  and no-of-days-late <= 14 then category = '2' Else

            If no-of-days-late >= 15 and no-of-days-late <= 20 then category = '3'  Else

                category = '4'

      End If



      I'm sure this type of situation is what the load/declare statements are for(?) but I can't figure out quite how to do this.



      Thanks,




        • Re: Setting up a new data field based on an database field being read
          Shraddha Gajare

          In Load Script,

           

          If ( no-of-days-late <= 7, '1',

             If (no-of-days-late >= 8  and  no-of-days-late <= 14, '2' ,

                If( no-of-days-late >= 15 and no-of-days-late <= 20 , '3' ,'4'))) as Category

            • Re: Setting up a new data field based on an database field being read
              Stephen Hasson

              Thanks for your reply.

               

              Can you please help to show this in context, as I don’t follow, sorry.

               

              My script is shown below.  The field I’m trying to use is actually ‘OtifDays’.

               

              To make the example simpler, if I want a new field of ‘OtifCategory’, to be ‘1’ if ‘OtifDays’ is <=7 and ‘2’ if >7, how would this look in the code?

               

              Where do I put in the script and where does the value ‘OtifCategory’ become available, as an available dimension in my sheet?

               

              Do I use ‘LOAD’ or ‘SET’ or another command?

               

               

               

              I appreciate your help.

               

               

              SET ThousandSep=',';

              SET DecimalSep='.';

              SET MoneyThousandSep=',';

              SET MoneyDecimalSep='.';

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

              SET TimeFormat='hh:mm:ss';

              SET DateFormat='DD/MM/YYYY';

              SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

              SET FirstWeekDay=0;

              SET BrokenWeeks=0;

              SET ReferenceDay=4;

              SET FirstMonthOfYear=1;

              SET CollationLocale='en-GB';

              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';

               

               

              LIB CONNECT TO 'Microsoft_SQL_Server_ceuk-ep9-dbase';

               

              LOAD PONum,

                             POLine,

                             PORelNum,

                             PONumLineRelease,

                             VendorNum,

                             Name,

                             BuyerID,

                             InvoiceNum,

                             InvoiceLine,

                             InvoiceFlag,

                             RcptLineDescription,

                             PackSlip,

                             PackLine,

                             PartNo,

                             PartDescription,

                             OpenLineFlag,

                             RecievedFlag,

                             OpenHdrFlag,

                             OrderQty,

                             ReceivedQty,

                             OrderDate,

                             DueDate,

                             ReceiptDate,

                             OtifDays,

                             FiscalYear,

                             FiscalPeriodText;

               

              [QlikView-PurchaseReceipts]:

              SELECT PONum,

                             POLine,

                             PORelNum,

                             PONumLineRelease,

                             VendorNum,

                             Name,

                             BuyerID,

                             InvoiceNum,

                             InvoiceLine,

                             InvoiceFlag,

                             RcptLineDescription,

                             PackSlip,

                             PackLine,

                             PartNo,

                             PartDescription,

                             OpenLineFlag,

                             RecievedFlag,

                             OpenHdrFlag,

                             OrderQty,

                             ReceivedQty,

                             OrderDate,

                             DueDate,

                             ReceiptDate,

                             OtifDays,

                             FiscalYear,

                             FiscalPeriodText

              FROM Epicor905.dbo."QlikView-PurchaseReceipts";

                • Re: Setting up a new data field based on an database field being read
                  Shraddha Gajare


                  Try:



                  SET ThousandSep=',';

                  SET DecimalSep='.';

                  SET MoneyThousandSep=',';

                  SET MoneyDecimalSep='.';

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

                  SET TimeFormat='hh:mm:ss';

                  SET DateFormat='DD/MM/YYYY';

                  SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

                  SET FirstWeekDay=0;

                  SET BrokenWeeks=0;

                  SET ReferenceDay=4;

                  SET FirstMonthOfYear=1;

                  SET CollationLocale='en-GB';

                  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';

                   

                   

                  LIB CONNECT TO 'Microsoft_SQL_Server_ceuk-ep9-dbase';

                   

                  LOAD PONum,

                                 POLine,

                                 PORelNum,

                                 PONumLineRelease,

                                 VendorNum,

                                 Name,

                                 BuyerID,

                                 InvoiceNum,

                                 InvoiceLine,

                                 InvoiceFlag,

                              If ( OtifDays <= 7, '1',

                                                If (OtifDays>= 8  and  OtifDays<= 14, '2' ,

                                                                If( OtifDays>= 15 and OtifDays <= 20 , '3' ,'4'))) as OtifCategory,

                                 RcptLineDescription,

                                 PackSlip,

                                 PackLine,

                                 PartNo,

                                 PartDescription,

                                 OpenLineFlag,

                                 RecievedFlag,

                                 OpenHdrFlag,

                                 OrderQty,

                                 ReceivedQty,

                                 OrderDate,

                                 DueDate,

                                 ReceiptDate,

                                 OtifDays,

                                 FiscalYear,

                                 FiscalPeriodText;

                   

                  SELECT PONum,

                                 POLine,

                                 PORelNum,

                                 PONumLineRelease,

                                 VendorNum,

                                 Name,

                                 BuyerID,

                                 InvoiceNum,

                                 InvoiceLine,

                                 InvoiceFlag,

                                 RcptLineDescription,

                                 PackSlip,

                                 PackLine,

                                 PartNo,

                                 PartDescription,

                                 OpenLineFlag,

                                 RecievedFlag,

                                 OpenHdrFlag,

                                 OrderQty,

                                 ReceivedQty,

                                 OrderDate,

                                 DueDate,

                                 ReceiptDate,

                                 OtifDays,

                                 FiscalYear,

                                 FiscalPeriodText

                  FROM Epicor905.dbo."QlikView-PurchaseReceipts";