Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a requirement to Map or copy the previous row of the table if for next Date, data is not be available.
I have a Stock Table with some fields like Key, Invoice date, Material and Stock etc. This table is mapped with an external calender date.
So here calender date is available from Date of year Start till today date. But Invoice date is available in only those cases when Invoice has been done. So In Input table maximum records (Rows) are Null because Calender Date is available for all the dates but Invoice date is available in case of very less records.
Here My Requirement is that if key and Stock (row is null except Calender date) is Null in this case for next dates we need to consider the same column till the next available key and Stock. We need to follow this process for whole year for all the keys.
Kindly find the attached excel Input table with Output Table.
Regards
Rishi
Hi Rishi,
Try the code below:
sample:
LOAD [Calender Date],
Key,
[Invoice Date],
Material,
Stock,
field6,
field7
FROM
(ooxml, embedded labels, table is [Input Table Table]);
NoConcatenate
data:
Load
[Calender Date],
if (len(Key)>0,Key,peek(Key)) as Key,
if (len([Invoice Date])>0,[Invoice Date],peek([Invoice Date])) as [Invoice Date],
if (len(Material)>0,Material,peek(Material)) as Material,
if (len(Stock)>0,Stock,peek(Stock)) as Stock,
if (len(field6)>0,field6,peek(field6)) as field6,
if (len(field7)>0,field7,peek(field7)) as field7;
Load
*
Resident sample;
drop table sample;
// end of script
The babove gives methe following
Hi Rishi,
Try the code below:
sample:
LOAD [Calender Date],
Key,
[Invoice Date],
Material,
Stock,
field6,
field7
FROM
(ooxml, embedded labels, table is [Input Table Table]);
NoConcatenate
data:
Load
[Calender Date],
if (len(Key)>0,Key,peek(Key)) as Key,
if (len([Invoice Date])>0,[Invoice Date],peek([Invoice Date])) as [Invoice Date],
if (len(Material)>0,Material,peek(Material)) as Material,
if (len(Stock)>0,Stock,peek(Stock)) as Stock,
if (len(field6)>0,field6,peek(field6)) as field6,
if (len(field7)>0,field7,peek(field7)) as field7;
Load
*
Resident sample;
drop table sample;
// end of script
The babove gives methe following
Hi Felip,
Thanks a lot!
I am getting correct result in this scenario. But my requirement is that in Input table I have no Calender Date field. So we need to map it through MasterCalender. But same output is required.
Now kindly find the input and output file.
! !
Regards
Rishi