Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have source as following:
ID | Date | Product |
2433266 | 8/09/2017 | P1 |
2433266 | P2 | |
2433266 | P3 | |
2433266 | P4 |
I am unable to write the script to load the date as following. Please help
ID | Date | Product |
2433266 | 8/09/2017 | P1 |
2433266 | 8/09/2017 | P2 |
2433266 | 8/09/2017 | P3 |
2433266 | 8/09/2017 | P4 |
Just add one more mapping table like the below and make the data blank if the ID has no data
Map:
Mapping LOAD Distinct
Id,
max(Date) as Key
FROM
[Output.xlsx]
(ooxml, embedded labels, table is Sheet2)
Group by Id;
LOAD Id,
if( isnull(applymap('Map',Id)) ,'', Date) as Date,
Product
FROM
[Output.xlsx]
(ooxml, embedded labels, table is Sheet2, filters(
Replace(2, top, StrCnd(null))
))
;
LOAD
ID,
If(Len(Date)=0, Peek(Date), Date) as Date,
Product
INLINE [
ID, Date, Product
2433266, 8/09/2017, P1
2433266, , P2
2433266, , P3
2433266, , P4
2433267, 15/09/2017, P1
2433267, , P2
2433267, , P3
2433267, , P4
];
Hi,
may be like this
LOAD ID,Product,
If(ID=Previous(ID),Peek(Date),Date) as Date Inline [
ID, Date, Product
2433266, 8/09/2017, P1
2433266,, P2
2433266,, P3
2433266,, P4];
Regards,
Antonio
Thanks for the reply Martinez !
But I have several ID's to be loaded along with the other columns in the table.
Is there an alternative solution ?
Cheers.
Thank you for your reply !
My data is in an excel file and I have several ID's to be loaded with the dates.
I just did some research and found out that INLINE cannot be used to load from file ?
If so any other alternative ?
Change Inline [...] to From File Excel
or provide Your source File.
You can do that with other sources, like :
LOAD
ID,
If(Len(Date)=0, Peek(Date), Date) as Date,
Product
;
Load
*
from c:\MyXL.xls (biff, embedded labels, table is Sheet1$)
;
But pay attention of the order by (I think ID, Date)
Load
[ID],
If(Len(date)=0, Peek(date), date) as DateTest,
Product as ProductType;
Load *
FROM
(
where Match([type],'x','y','z');
Tried above code, it is loading just like old times. Am I Missing anything here ? Note: My csv file have more than 30 columns.
Could you please help me ?
May be just use the enable transformation to achieve this . click on the fill tab and click the cell condition and click ok
as shown in the below screen
You will get the data as below
Thank you very much susant !
This option fills all the cells, even the ID's without dates.
How do we avoid those ID's, which have Date's empty ?