Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Generating dimensions values depending on another dimension values

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:

ScreenShot065.jpg

Is there a way to do this?

Thanks in advanced!


1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_136387_Pic1.JPG.jpg

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

View solution in original post

9 Replies
anbu1984
Master III
Master III

Check this

maxgro
MVP
MVP

maybe this calculated dimension

=if(aggr(count(distinct {$ <Activity={Done}>} Activity), ID)=1, 'Done', 'Undone')

1.png

Not applicable
Author

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":

ScreenShot066.jpg

Not applicable
Author

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.

ScreenShot066.jpg

nagaiank
Specialist III
Specialist III

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;

Not applicable
Author

It didn't work. In my real data, there's others dimensions, not only ID and Activity.

MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_136387_Pic1.JPG.jpg

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

Not applicable
Author

It worked fine, Marco!

Thank you!!!

MarcoWedel

you're welcome

regards

Marco