Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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....
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;
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;
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;