Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In the script, for a field I would like to use the data from one field or another depending of the value of this field.
For example,
If the value inside the Item field is 1, I would like to use the column StartDate, and if the value inside the Item field is 2, I would like to use the data from EndDate column
Item | Date |
1 | StartDate |
2 | EndDate |
Obviously this should be automated and continue working in case of adding another item. I don't want to do this by adding the literal.
Thank you.
Hello,
Try something like this:
Fields:
Mapping
Load * Inline
[
Item, Date
1, StartDate
2, EndDate
];
Data:
Load * Inline
[Item, StartDate, EndDate
1, 11/06/2018, 12/06/2018
2, 09/06/2018, 10/06/2018
1, 07/06/2018, 08/06/2018
2, 06/06/2018, 07/06/2018
];
FinalDate:
Load *,
Peek(applymap('Fields', [Item], null()), RowNo() - 1, 'Data') as [Date]
Resident Data;
Drop Table Data;
Result:
I think you can create a mappingtable/applymap for this and put the outcome between [] to use the outcome as field name. Not sure, so you will have to try and see if it works.
Not working an applymap here. I have created the following Applymap
XXXXXX:
Mapping LOAD
Item,
Date
FROM [xxxx.xlsx]
(ooxml, embedded labels, table is xxxx);
and later the followinf line for the Applymap
applymap('XXXXXX', [Item], null()) as [Date]
but what I get for Item 1 is the descriptor StartDate and not the value of the column StartDate
with and if it would be something like that
if(Item='1', StartDate, if(Item='2', EndDate)) as Date
the thing is that I would like to have this in an automatic way and not add everytime there is a new Item a new line to the if
Hello,
Try something like this:
Fields:
Mapping
Load * Inline
[
Item, Date
1, StartDate
2, EndDate
];
Data:
Load * Inline
[Item, StartDate, EndDate
1, 11/06/2018, 12/06/2018
2, 09/06/2018, 10/06/2018
1, 07/06/2018, 08/06/2018
2, 06/06/2018, 07/06/2018
];
FinalDate:
Load *,
Peek(applymap('Fields', [Item], null()), RowNo() - 1, 'Data') as [Date]
Resident Data;
Drop Table Data;
Result:
Perfectly working.
Thank you so much Daniel!!