Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi again,
I'm working with data that contains a set of Orders. Each Order has an Order ID, and a single Screen Status. What I want to do is then assign another value to different screen statuses called Advice Status. This is simple enough, and i've done it in an Excel file; two columns, screen status and advice status, with each relation mapped out.
This works well, however, there are some orders where the screen status is null. For this case I still want to assign the order with an Advice status of "Not Validated." I tried doing this with a row in the excel file that has a blank Screen Status, but qlikview won't import that row. Does anyone know how I can do this assignment? I was thinking if statements in the load to assign a value to orders with a null Screen Status, but I'm not sure how to do this.
Thanks,
Dan
Rather than test for null as there are differences between the way 32bit and 64bit recognise it, its safer to do a length test like;
if(len([FIELDNAME]) = 0), 'This field is null', FIELDNAME) as FIELDNAME
if you also want to include a space filled column then use:
if(len(trim([FIELDNAME])) = 0), 'This field is null or spaces', FIELDNAME) as FIELDNAME
Regards,
Gordon
If i'm reading this correctly you just want to assign a value to a null field.
if(isnull([FIELDNAME]) = -1), 'This field is null', FIELDNAME) as FIELDNAME
Rather than test for null as there are differences between the way 32bit and 64bit recognise it, its safer to do a length test like;
if(len([FIELDNAME]) = 0), 'This field is null', FIELDNAME) as FIELDNAME
if you also want to include a space filled column then use:
if(len(trim([FIELDNAME])) = 0), 'This field is null or spaces', FIELDNAME) as FIELDNAME
Regards,
Gordon
my current syntax is this:
LOAD [Screen Status] as [Original Screen Status],
[Advice Status] as [Original Advice Status]
FROM Screens.xls (biff, embedded labels, table is [Screen Advice Status$]);
where does that if statement fit into this? What I want it to represent is, if the Screen Status is null, then assign a value of 'Not Validated' to Original Advice Status, otherwise give it Advice Status. Here is what I've edited in, but it still doesn't seem to work, the orders that have a null screen status are still displaying a null advice status
LOAD [Screen Status] as [Original Screen Status],
if(len(trim([Screen Status])) = 0, 'Not Validated', [Advice Status]) as [Original Advice Status]
FROM Screens.xls (biff, embedded labels, table is [Screen Advice Status$]);
Dan,
you need something similar in your Orders load - replace null (or missing) screen status for the Order with the text "Not Validated" or someting like that...
Oleg
ah ic, that's a good idea. I'll give a value to null screen numbers, and then give orders with the null screen number a screen status of 'Not Validated' based on that value.
Thanks guys!