FILLING NULL VALUES

[#1]

Hi

I have an issue with filling null values in the table.

Please find attached sample document with a QVD  file for information. As you can see there are some null values against date and divisions.

I want to put the previous value (for null value fields)against the division until the next available value in the table. The reason is I need to record a value every single day from the start date to finish date for every single location. If there is no values against a division, I want to pick up the previous value against the division

Hope you will understand what I try to achieve.

Can you please advice.

Regards

Harsh

[swuehl · Accepted solution]

Maybe like this:

T1:

LOAD [Division Code],

    JOB_TRANS_DATE,

    JOB_NET_VALUE

FROM

[Fuel_Transaction.qvd]

(qvd);

T2:

NOCONCATENATE

LOAD  [Division Code],

  JOB_TRANS_DATE,

  If(len(trim(JOB_NET_VALUE)),JOB_NET_VALUE, if(Peek([Division Code])=[Division Code],Peek(JOB_NET_VALUE))) as JOB_NET_VALUE

RESIDENT T1 

ORDER BY

[Division Code], JOB_TRANS_DATE;

DROP TABLE T1;

[#3]

Thanks a lot. It works.