Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone, I need your helpyou in this case. I need to create a pipeline with the contract services that actually I've. I've a dataset with this:
ID_Contract | CstmrCode | CstmrName | Cluster | CreateDate | EndDate | $$$ |
20089 | 1 | A | 1 | 01-09-2017 | 31-08-2020 | $352.000 |
21122 | 2 | B | 2 | 01-09-2017 | 01-09-2020 | $166.666 |
23537 | 3 | C | 4 | 19-04-2018 | 31-03-2021 | $352.000 |
In the first row, I've a contract service, it's started in 1/9/2017 (dd/mm/yyyy) and finish at 31-08-2020. All the contracts are monthly invoiced.
I need create a table with this characteristics:
ID | CstmrCode | CstmrName | Cluster | CreateDate | EndDate | FlowDate | $$$ |
20089 | 1 | A | 1 | 01-09-2017 | 31-08-2020 | 01-09-2017 | $352.000 |
20089 | 1 | A | 1 | 01-09-2017 | 31-08-2020 | 01-10-2017 | $352.000 |
20089 | 1 | A | 1 | 01-09-2017 | 31-08-2020 | 01-11-2017 | $352.000 |
… | … | … | … | … | … | … | … |
20089 | 1 | A | 1 | 01-09-2017 | 31-08-2020 | 01-07-2020 | $352.000 |
20089 | 1 | A | 1 | 01-09-2017 | 31-08-2020 | 01-08-2020 | $352.000 |
Thanks for all.
Blessings.
try below
Data:
LOAD ID_Contract,
CstmrCode,
CstmrName,
Cluster,
CreateDate,
monthstart(CreateDate,IterNo()-1) as Flow_Date,
EndDate,
$$$
FROM Table
while monthstart(CreateDate,IterNo()-1)<= EndDate;
try below
Data:
LOAD ID_Contract,
CstmrCode,
CstmrName,
Cluster,
CreateDate,
monthstart(CreateDate,IterNo()-1) as Flow_Date,
EndDate,
$$$
FROM Table
while monthstart(CreateDate,IterNo()-1)<= EndDate;
Thaks so much, it was very usefull your solution. How would it be done so that the flow-date is quarterly or semi-annual?
Quarterly example:
-This is the exam contract=
D_Contract | CstmrCode | CstmrName | Cluster | CreateDate | EndDate | $$$ |
20089 | 1 | A | 1 | 01-08-2020 | 31-07-2021 | $352.000 |
-This is my goal's example=
ID | CstmrCode | CstmrName | Cluster | CreateDate | EndDate | FlowDate | $$$ |
20089 | 1 | A | 1 | 01-08-2020 | 31-07-2021 | 01-08-2020 | $352.000 |
20089 | 1 | A | 1 | 01-08-2020 | 31-07-2021 | 01-12-2020 | $352.000 |
20089 | 1 | A | 1 | 01-08-2020 | 31-07-2021 | 01-04-2021 | $352.000 |