topic Populate data inbetween date ranges in QlikView

Populate data inbetween date ranges

Dayna — Tue, 25 Aug 2015 11:17:12 GMT

Hello All!

I'm hoping you can help me... I had a set of data with information when the data changes, but what I need to do is fill in the blanks for the dates inbetween where no information exists.

On the attached spreadsheet, the driving data is the ld_part1 and ld_ref1 record, so on the example of ref = 51501622, there's a record missing for the 24/08/2015 - I want to show a record for this date with the information from the last record on the previous day.

Please help! If you need any more information, please ask.

Kind Regards,

Dayna

Re: Populate data inbetween date ranges

maxgro — Tue, 25 Aug 2015 12:27:55 GMT

see attachment

excel:

LOAD

ld_part1,

ld_ref1,

ld_domain1,

ld_loc1,

ld_lot1,

ld_qty_all1,

ld_qty_oh1,

ld_site1,

ld_status1,

snap_date1

FROM

[Example Data.xls]

(biff, embedded labels, table is Sheet1$);

minmaxdate:

load

ld_part1,

ld_ref1,

min(snap_date1) as mindate,

max(snap_date1) as maxdate

Resident excel

group by ld_part1, ld_ref1 ;

f1:

load

ld_part1,

ld_ref1,

date(mindate + iterno() -1) as snap_date1

Resident minmaxdate

While mindate + IterNo() -1 <= maxdate;

DROP Table minmaxdate;

left join (f1) load * Resident excel;

DROP Table excel;

f2:

NoConcatenate load

ld_part1,

ld_ref1,

if(isnull(ld_loc1) and peek(ld_part1)=ld_part1 and peek(ld_ref1)=ld_ref1, peek(ld_domain1), ld_domain1) as ld_domain1,

if(isnull(ld_domain1) and peek(ld_part1)=ld_part1 and peek(ld_ref1)=ld_ref1, peek(ld_loc1), ld_loc1) as ld_loc1,

if(isnull(ld_lot1) and peek(ld_part1)=ld_part1 and peek(ld_ref1)=ld_ref1, peek(ld_lot1), ld_lot1) as ld_lot1,

if(isnull(ld_qty_all1) and peek(ld_part1)=ld_part1 and peek(ld_ref1)=ld_ref1, peek(ld_qty_all1), ld_qty_all1) as ld_qty_all1,

if(isnull(ld_qty_oh1) and peek(ld_part1)=ld_part1 and peek(ld_ref1)=ld_ref1, peek(ld_qty_oh1), ld_qty_oh1) as ld_qty_oh1,

if(isnull(ld_site1) and peek(ld_part1)=ld_part1 and peek(ld_ref1)=ld_ref1, peek(ld_site1), ld_site1) as ld_site1,

if(isnull(ld_status1) and peek(ld_part1)=ld_part1 and peek(ld_ref1)=ld_ref1, peek(ld_status1), ld_status1) as ld_status1,

snap_date1

Resident f1

order by ld_part1, ld_ref1, snap_date1;

DROP Table f1;

Re: Populate data inbetween date ranges

Anonymous — Tue, 25 Aug 2015 12:46:15 GMT

Hi Dayna,

Please try:

Data:

LOAD ld_part1 & ld_ref1 & ld_site1 as Key,

ld_domain1,

ld_loc1,

ld_lot1,

ld_part1,

ld_qty_all1,

ld_qty_oh1,

ld_ref1,

ld_site1,

ld_status1,

snap_date1

FROM

(biff, embedded labels, table is Sheet1$);

Left join(Data)

LOAD Key,

Date(min(snap_date1),'DD/MM/YYYY') as MinDate,

Date(max(snap_date1),'DD/MM/YYYY') as MaxDate

Resident Data

where ld_part1 = 'DQ5931FWB' and ld_ref1 = '51501622' and ld_site1 = 'MAG'

Group by Key

order by Key asc;

NoConcatenate

Table:

LOAD Key,

ld_domain1,

ld_loc1,

ld_lot1,

ld_part1,

ld_qty_all1,

ld_qty_oh1,

ld_ref1,

ld_site1,

ld_status1,

Date(snap_date1+IterNo()-1) as snap_date1

While IterNo()=1 or Key=Previous(Key) and MinDate + IterNo()-1 < MaxDate;

LOAD *

Resident Data

where ld_part1 = 'DQ5931FWB' and ld_ref1 = '51501622' and ld_site1 = 'MAG'

Order by snap_date1 asc;

Join(Table)

LOAD *

Resident Data;

DROP Tables Data;

Regards

Neetha

Re: Populate data inbetween date ranges

Dayna — Tue, 25 Aug 2015 12:54:29 GMT

Thank you for your help!! Looks good to me!