Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a SourceData table:
Id | IsFlag | Child | Parent | Date |
1 | 1 | 0 | 2536 | 01/01/2020 |
2 | 0 | 2536 | 101 | 01/01/2020 |
3 | 0 | 2536 | 102 | 01/01/2020 |
4 | 0 | 0 | 103 | 01/01/2020 |
5 | 1 | 0 | 2550 | 01/01/2020 |
5 | 0 | 2550 | 105 | 01/01/2020 |
14 | 0 | 0 | 114 | 01/01/2020 |
5 | 1 | 0 | 2550 | 20/01/2020 |
5 | 0 | 2550 | 105 | 20/01/2020 |
I want to add a new column based on two columns Child and Parent. The new column will reflect a group of rows for a common Parent as it's shown below:
Id | IsFlag | Child | Parent | Date | Result |
1 | 1 | 0 | 2536 | 01/01/2020 | 2536 |
2 | 0 | 2536 | 101 | 01/01/2020 | 2536 |
3 | 0 | 2536 | 102 | 01/01/2020 | 2536 |
4 | 0 | 0 | 103 | 01/01/2020 | 103 |
5 | 1 | 0 | 2550 | 01/01/2020 | 2550 |
5 | 0 | 2550 | 105 | 01/01/2020 | 2550 |
14 | 0 | 0 | 114 | 01/01/2020 | 114 |
5 | 1 | 0 | 2550 | 20/01/2020 | 2550 |
5 | 0 | 2550 | 105 | 20/01/2020 | 2550 |
I tried to do it with For .. Next loop, but it works very slow:
Let vNumRows = NoOfRows('SourceData');
For i = 0 to $(vNumRows)
Let vCurrentParent = Peek('Parent', $(i), 'SourceData');
Let vCurrentIsFlag = Peek('IsFlag', $(i), 'SourceData');
Let vCurrentDate = Peek('Date', $(i), 'SourceData');
If (vCurrentIsFlag = 1) THEN // if record is Parent
LoopNewData:
LOAD IsFlag,
Child,
Parent,
Id,
'$(vCurrentParent)' AS Result,
Date
RESIDENT SourceData
WHERE (Child = '$(vCurrentParent)' OR Parent= '$(vCurrentParent)')
AND Date= '$(vCurrentDate)';
END IF;
NEXT
//get records without child rows
LoopNewData:
LOAD IsFlag,
Child,
Parent,
Id,
Parent AS Result,
Date
RESIDENT SourceData
WHERE (Child = 0 AND IsFlag = 0) ;
DROP TABLE SourceData;
Do you have any suggestions on how to get better performance?
Not sure why you need a loop here.. can this work?
Table:
LOAD *,
If(Child <> 0, Child, Parent) as Result;
LOAD * INLINE [
Id, IsFlag, Child, Parent, Date
1, 1, 0, 2536, 01/01/2020
2, 0, 2536, 101, 01/01/2020
3, 0, 2536, 102, 01/01/2020
4, 0, 0, 103, 01/01/2020
5, 1, 0, 2550, 01/01/2020
5, 0, 2550, 105, 01/01/2020
14, 0, 0, 114, 01/01/2020
5, 1, 0, 2550, 20/01/2020
5, 0, 2550, 105, 20/01/2020
];
Not sure why you need a loop here.. can this work?
Table:
LOAD *,
If(Child <> 0, Child, Parent) as Result;
LOAD * INLINE [
Id, IsFlag, Child, Parent, Date
1, 1, 0, 2536, 01/01/2020
2, 0, 2536, 101, 01/01/2020
3, 0, 2536, 102, 01/01/2020
4, 0, 0, 103, 01/01/2020
5, 1, 0, 2550, 01/01/2020
5, 0, 2550, 105, 01/01/2020
14, 0, 0, 114, 01/01/2020
5, 1, 0, 2550, 20/01/2020
5, 0, 2550, 105, 20/01/2020
];
Thank you very much!
It works!