Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Scripting Master,
Is it possible if I have a data like this:
What load script should I use to produce output like this?
Thank you for any of your solution.
Something like this? PFA
Original:
(Your Table)
Temp1:
LOAD
If(isNull(NoteCat), Peek('NoteCat'), NoteCat) as NoteCat,
Note,
Date;
LOAD Note,
Date,
If(Len(Trim(Date)) = 0, Note) as NoteCat
Resident Original
Where Len(Trim(Note)) > 0;
Drop table Original;
Final:
LOAD
NoteCat & ' - ' & Note as Note,
Date
Resident Temp1
Where NoteCat <> Note;
Drop table Temp1;
In Temp1, I used a preceding load to first load in a table with If(Len(Trim(Date)) = 0, Note) as NoteCat. This creates a table with the original 2 fields and a field that uses the Note for NoteCat if the Date field is empty for that row. The Where clause also gets rid of the blank rows. Next, still in Temp1, I load all fields from that, replacing all null values in NoteCat with the most recent non null NoteCat. So Temp1 has fields NoteCat, Note, and Date.
Next I just needed to combine the NoteCat and Note fields where NoteCat and Note aren't the same. This leaves the table you wanted.
Hope this works for you, if not let me know
Enable transformation Step in the Load Script Wizard
Column Tab-New...
Source Column-1
Replace Null Value with Previous Value-check
Cells from these rows... Press a button
select Compare with value
Column-1 equal to value is 'Animal'
Condition Press Add button
OK
We will repeat three types, three columns, four columns, and five column values.
I don't understand English well.
Thank you for your answer. But I think there is not kind of transformation.
In my sample data and output, there is no change in the number or format of the field.
Something like this? PFA
Original:
(Your Table)
Temp1:
LOAD
If(isNull(NoteCat), Peek('NoteCat'), NoteCat) as NoteCat,
Note,
Date;
LOAD Note,
Date,
If(Len(Trim(Date)) = 0, Note) as NoteCat
Resident Original
Where Len(Trim(Note)) > 0;
Drop table Original;
Final:
LOAD
NoteCat & ' - ' & Note as Note,
Date
Resident Temp1
Where NoteCat <> Note;
Drop table Temp1;
In Temp1, I used a preceding load to first load in a table with If(Len(Trim(Date)) = 0, Note) as NoteCat. This creates a table with the original 2 fields and a field that uses the Note for NoteCat if the Date field is empty for that row. The Where clause also gets rid of the blank rows. Next, still in Temp1, I load all fields from that, replacing all null values in NoteCat with the most recent non null NoteCat. So Temp1 has fields NoteCat, Note, and Date.
Next I just needed to combine the NoteCat and Note fields where NoteCat and Note aren't the same. This leaves the table you wanted.
Hope this works for you, if not let me know
That's what I expected.
Thank you Jens.