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

[stephenhasson]

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,

[shraddha_g]

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

[stephenhasson]

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

[shraddha_g]

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

[stephenhasson]

Fantastic, thank you very much. .

It's much clearer now. I wasn't understanding to include in the existing load statement