How to get a value based on two columns

daria — Sat, 16 Nov 2024 18:05:28 GMT

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?

Re: How to get a value based on two columns

sunny_talwar — Fri, 18 Sep 2020 17:29:46 GMT

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

Re: How to get a value based on two columns

daria — Fri, 18 Sep 2020 19:26:01 GMT

Thank you very much!

It works!

topic Re: How to get a value based on two columns in App Development

How to get a value based on two columns

Re: How to get a value based on two columns

Re: How to get a value based on two columns