Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I need suggestions in getting below requirement.
I need to fill the dates in between ValidFrom and ValidTo dates
Example:
Part Supplier Plant Validfrom ValidTo Price
Screw ABC XYZ 01/04/2020 30/06/2020 100
the above is the sample data from which I need to derive a new column
Required Output:
Part Supplier Plant Validfrom ValidTo Price NewColumn
Screw ABC XYZ 01/04/2020 30/06/2020 100 01/04/2020
Screw ABC XYZ 01/04/2020 30/06/2020 100 02/04/2020
Screw ABC XYZ 01/04/2020 30/06/2020 100 03/04/2020
Screw ABC XYZ 01/04/2020 30/06/2020 100 04/04/2020
Screw ABC XYZ 01/04/2020 30/06/2020 100 05/04/2020
.
.
.
. so on
Screw ABC XYZ 01/04/2020 30/06/2020 100 30/06/2020
Maybe like this:
TEST:
LOAD * INLINE [
Part, Supplier, Plant, Validfrom, ValidTo, Price
Screw, ABC, XYZ, 01/04/2020, 30/06/2020, 100
];
NoConcatenate
tmp:
LOAD
Part,
Supplier,
Plant,
Validfrom,
ValidTo,
Validfrom + IterNo()-1 as Validfrom_New,
Validfrom + IterNo()-1 as ValidTo_New,
Price
Resident TEST
While Validfrom +IterNo()-1 <= ValidTo;
NoConcatenate
Final:
Load *, Date(ValidTo_New,'DD/MM/YYYY') as DateNew Resident tmp ; DROP Tables TEST,tmp