Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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";
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";
Fantastic, thank you very much. .
It's much clearer now. I wasn't understanding to include in the existing load statement