Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This si just an example to try and achieve what I can not achieve with a much larger file
Script is shown below
Ive tried every option I can from the manuals but this is the best so far that I can achieve
I just want null to appear instead of blank in the LU column (so that I can select by null. Blank of course is not a selectable field)
I have just been dumpinmg into excel and sorting but can be done in qlikview?
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/YYYYhh:mm:ss[.fff]';SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';SET NullValue= 'Null';
abc:LOAD
Ser_Num
FROM
D:\QlikView\TestSerialRH.xlsx
(ooxml, embedded labels, table is Sheet1);
xyz:
LEFT JOIN
LOAD Ser_Num,
LUFROM
D:\QlikView\TestSerialRH.xlsx
(ooxml, embedded labels, table is Sheet2);
NULLASVALUE LU;
lOAD LU,Ser_Num
Resident abc;
OK DONE
NULLASVALUE LU2;
lOAD
LU as LU2,
Ser_Num
Resident abc;
GIVES
Ser_Num | LU | LU2 |
AA | RH | RH |
BB | RH | RH |
CC | DH | DH |
DD | DH | DH |
EE | Null | |
FF | Null | |
GG | Null | |
HH | Null | |
II | Null | |
JJ | Null | |
KK | Null | |
LL | Null | |
MM | Null | |
Null | ||
And what I goit from thsi
But I only want the null field to appear as NULL not null and blank
I can seelct the null values in column LU based on thsi approach but I would rather the blank column were not included
Thanks for any help
Ser_Num | LU |
AA | RH |
BB | RH |
CC | DH |
DD | DH |
EE | Null |
EE | |
FF | Null |
FF | |
GG | Null |
GG | |
HH | Null |
HH | |
II | Null |
II | |
JJ | Null |
JJ | |
KK | Null |
KK | |
LL | Null |
LL | |
MM | Null |
MM | |
Null |
OK DONE
NULLASVALUE LU2;
lOAD
LU as LU2,
Ser_Num
Resident abc;
GIVES
Ser_Num | LU | LU2 |
AA | RH | RH |
BB | RH | RH |
CC | DH | DH |
DD | DH | DH |
EE | Null | |
FF | Null | |
GG | Null | |
HH | Null | |
II | Null | |
JJ | Null | |
KK | Null | |
LL | Null | |
MM | Null | |
Null | ||