Populate data inbetween date ranges

[Dayna]

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

[maxgro · Accepted solution]

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;

[Anonymous]

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

[Dayna]

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