How to get a value based on two columns

[daria]

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?

[sunny_talwar · Accepted solution]

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
];

[daria]

Thank you very much!

It works!