Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have two dimensions: "ID" and "Activity". For each distinct ID there is a lot of activities. When one activity is finished, we add a value called "Done".
I'd like to create a new dimension called "Status", in which we can have a "Done" value associated to a IDs that contains the "Done" value in one of the "Activity" values. If we don't have it, the other value is "Undone".
Bellow, you can see more clearly what I'm looking for:
Is there a way to do this?
Thanks in advanced!
Hi,
one solution might be:
tabInput:
LOAD * Inline [
ID, Activity
A, Activity_1
A, Activity_2
A, Activity_3
A, Activity_4
A, Done
B, Activity_1
B, Activity_2
C, Activity_1
C, Activity_2
C, Done
];
Left Join (tabInput)
LOAD ID,
If(Sum(Activity='Done'),'Done','Undone') as Status
Resident tabInput
Group By ID;
hope this helps
regards
Marco
Check this
maybe this calculated dimension
=if(aggr(count(distinct {$ <Activity={Done}>} Activity), ID)=1, 'Done', 'Undone')
It works on tables.
But what I'm looking for is a new dimension createad on load script.
Then, I could have a list with "Done" and "Undone":
Hello Massimo,
It works fine on tables too.
But I need a dimension created on the load script. Then, I could have the dimension "Status", with two values: Done and Undone.
If the field Status has to be created using a script, you may try a script like the following:
Data:
LOAD * Inline [
ID, Activity
A,Activity_1
A,Activity_2
A,Activity_3
A,Activity_4
A,Done
B,Activity_1
B,Activity_2
C,Activity_1
C,Activity_2
C,Done
];
Temp:
LOAD ID, Activity as Status Resident Data Where Activity = 'Done';
Left Join (Data) LOAD * Resident Temp;
DROP Table Temp;
Final:
NoConcatenate
LOAD ID, Activity,If(Status='Done',Status,'Undone') as Status Resident Data;
DROP Table Data;
It didn't work. In my real data, there's others dimensions, not only ID and Activity.
Hi,
one solution might be:
tabInput:
LOAD * Inline [
ID, Activity
A, Activity_1
A, Activity_2
A, Activity_3
A, Activity_4
A, Done
B, Activity_1
B, Activity_2
C, Activity_1
C, Activity_2
C, Done
];
Left Join (tabInput)
LOAD ID,
If(Sum(Activity='Done'),'Done','Undone') as Status
Resident tabInput
Group By ID;
hope this helps
regards
Marco
It worked fine, Marco!
Thank you!!!
you're welcome
regards
Marco