Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
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

Re: Generating dimensions values depending on another dimension values

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
Honored Contributor III

Re: Generating dimensions values depending on another dimension values

Check this

MVP
MVP

Re: Generating dimensions values depending on another dimension values

maybe this calculated dimension

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

1.png

Not applicable

Re: Generating dimensions values depending on another dimension values

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

Re: Generating dimensions values depending on another dimension values

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
Valued Contributor III

Re: Generating dimensions values depending on another dimension values

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

Re: Generating dimensions values depending on another dimension values

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

Re: Generating dimensions values depending on another dimension values

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

Not applicable

Re: Generating dimensions values depending on another dimension values

It worked fine, Marco!

Thank you!!!

Re: Generating dimensions values depending on another dimension values

you're welcome

regards

Marco