How do i make this script more efficiency? (Creating indicator if 1 value occurs)

[fishing_weights]

Original Table:

Date	Id_parent	Id_child	Original_Indicator
1 Jan 23	a	1	y
1 Jan 23	a	2	n
1 Jan 23	b	1	n
1 Jan 23	b	2	n
28 Feb 23	a	1	n
28 Feb 23	a	2	n

The result i want is to create another column if any of the Id_child is a "y" to tag all occurrence of the Id_parent as a "1" within that date

Final Table:

Date	Id_parent	Id_child	Original_Indicator	Final_indicator
1 Jan 23	a	1	y	1
1 Jan 23	a	2	n	1
1 Jan 23	b	1	n	0
1 Jan 23	b	2	n	0
28 Feb 23	a	1	n	0
28 Feb 23	a	2	n	0

The code I'm currently running:

[FINAL_INDICATOR]:
LOAD Date,Id_parent, If(ind >0,1,0) as Final_indicator;
LOAD DISTINCT Date,Id_parent,Sum(ind) as ind GROUP BY Date,Id_parent;
LOAD DISTINCT Date,Id_parent,Id_child,Original_Indicator,If(Match(Original_Indicator,'Y'),1,0) as ind;
Resident[ORIGINAL_TABLE];

[FINAL_TABLE]:
LOAD * Resident[ORIGINAL_TABLE];
left join
LOAD * Resident[FINAL_INDICATOR]; 

 note i've afew million rows so the ID is much much more....

[Chirantha]

In your current approach, you are loading the table multiple times, which may not be the most efficient way, especially when dealing with large datasets.

A more efficient way could be by creating the Final_Indicator in one step using advanced aggregation functions in the script. Qlik supports the aggregation function called Aggr(), which can be combined with MaxString(), to perform the operation in one load step.

Below is just an example of how you could execute it, you might want to change it a bit

FINAL_TABLE:
LOAD 
    Date,
    Id_parent,
    Id_child,
    Original_Indicator,
    If(MaxString(Aggr(If(Match(Original_Indicator, 'Y'), 1, 0), Id_parent, Date))=1, 1, 0) as Final_Indicator
RESIDENT ORIGINAL_TABLE;

[MayilVahanan · Accepted solution]

Hi

Try like below

MapFlag:

Mapping LOAD Distinct Date&Id_parent as Key, 1 as Flag
from ursource
where Original_Indicator = 'Y';

Load *, ApplyMap('MapFlag', Date&Id_parent, 0) as Final from ursource;