topic Re: Having Trouble Filling in Nulls in QlikView

Having Trouble Filling in Nulls

bnelson111 — Fri, 06 Mar 2015 09:28:40 GMT

I have a left join table were 041 is the stat-code and the description should read 'deleted' as i want to replace the Null currently reading against the 041 code. But the table is coming up as pic see below script??

Script

left join

LOAD "stat-code",

if(len("stat-code")='041','deleted',description) as description,

"type",

cost,

kco,

movement,

indirect,

"trans-cost",

"sundry-code",

"restrict-flag",

"sub-type",

PlantCode;

SQL SELECT *

FROM PUB.sfstatcode;

Re: Having Trouble Filling in Nulls

anbu1984 — Fri, 06 Mar 2015 09:40:36 GMT

if(Trim("stat-code")='041','deleted',description) as description,

Re: Having Trouble Filling in Nulls

bnelson111 — Fri, 06 Mar 2015 09:48:27 GMT

unfortunately didn't work

left join

LOAD "stat-code",

if(Trim("stat-code")='041','deleted',description) as description,

//description,

"type",

cost,

kco,

movement,

indirect,

"trans-cost",

"sundry-code",

"restrict-flag",

"sub-type",

PlantCode;

SQL SELECT *

FROM PUB.sfstatcode;

//-------- End Multiple Select Statements ------

Re: Having Trouble Filling in Nulls

Gysbert_Wassenaar — Fri, 06 Mar 2015 09:59:10 GMT

The length of a value will never be '041'. So you probably mean if("stat-code"='041', ...etc.

Is the field description part of the left table or of the PUB.sfstatcode? If that field only exists in the left table then you first have to join the tables before you can fill the nulls.

Re: Having Trouble Filling in Nulls

anbu1984 — Fri, 06 Mar 2015 09:59:43 GMT

Is stat-code join key?

Re: Having Trouble Filling in Nulls

CELAMBARASAN — Fri, 06 Mar 2015 10:02:55 GMT

May be stat-code = 041 exists only in the first table, which you do left join with

If that is the case then use the below code

Lets say first loaded table is Table1(before the left join)

left join

LOAD "stat-code",

description,

"type",

cost,

kco,

movement,

indirect,

"trans-cost",

"sundry-code",

"restrict-flag",

"sub-type",

PlantCode;

SQL SELECT *

FROM PUB.sfstatcode;

Left Join(Table1)

LOAD DISTINCT "stat-code",

if(Trim("stat-code")='041','deleted',description) as NewFieldDescription

Resident Table1;

Re: Having Trouble Filling in Nulls

chrislofthouse — Fri, 06 Mar 2015 10:03:11 GMT

Try this:

if(Trim([stat-code])=041,'deleted',description) as description,

Or if you can upload the app this would help diagnose the problem quicker.

Re: Having Trouble Filling in Nulls

bnelson111 — Fri, 06 Mar 2015 10:04:28 GMT

Yes

Re: Having Trouble Filling in Nulls

bnelson111 — Fri, 06 Mar 2015 10:06:51 GMT

Heres the two tables

[SFDC DATA]:

LOAD kco,

kjobcode,

kcompno,

"seq-code",

"mach-id",

"stat-code",

"op-code",

"start-time",

Interval#( "elapse-time",'s') as "Seconds",

"elapse-time",

"shift-code",

"extract-flag",

"non-prod-flag",

"seq-code2",

"overtime-flag",

kprocno,

"history-flag",

formno,

"event-type",

"substat-init",

"substat-final",

date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YYYY') as "End Date",

day(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Day Completed",

Month(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Month Completed",

year(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Year Completed",

amended,

"ass-no",

"group-code",

"seq-num",

"sub-type",

"comment-flag",

"group-ind",

PlantCode,

"event-qty",

"misc-qty",

"sundry-qty";

SQL SELECT *

FROM PUB.sfeventcds

WHERE "start-time" > '733682170';

left join

LOAD "stat-code",

description,

"type",

cost,

kco,

movement,

indirect,

"trans-cost",

"sundry-code",

"restrict-flag",

"sub-type",

PlantCode;

SQL SELECT *

FROM PUB.sfstatcode;

//-------- End Multiple Select Statements ------

Re: Having Trouble Filling in Nulls

anbu1984 — Fri, 06 Mar 2015 10:08:25 GMT

As Celambarasan said below, you may not have 041 in PUB.sfstatcode. Try as he suggested below