Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
Thank you for your help!! Looks good to me!