topic Re: Generating dimensions values depending on another dimension values in QlikView

Generating dimensions values depending on another dimension values

Sat, 04 Oct 2014 17:32:38 GMT

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!

Re: Generating dimensions values depending on another dimension values

anbu1984 — Sat, 04 Oct 2014 17:52:17 GMT

Check this

Re: Generating dimensions values depending on another dimension values

maxgro — Sat, 04 Oct 2014 17:58:19 GMT

maybe this calculated dimension

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

Re: Generating dimensions values depending on another dimension values

Sat, 04 Oct 2014 19:08:51 GMT

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

Re: Generating dimensions values depending on another dimension values

Sat, 04 Oct 2014 19:37:49 GMT

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.

Re: Generating dimensions values depending on another dimension values

nagaiank — Sat, 04 Oct 2014 21:16:56 GMT

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;

Re: Generating dimensions values depending on another dimension values

Sat, 04 Oct 2014 23:14:10 GMT

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

MarcoWedel — Sat, 04 Oct 2014 23:43:16 GMT

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

Re: Generating dimensions values depending on another dimension values

Sun, 05 Oct 2014 00:05:45 GMT

It worked fine, Marco!

Thank you!!!

Re: Generating dimensions values depending on another dimension values

MarcoWedel — Sun, 05 Oct 2014 07:49:27 GMT

you're welcome

regards

Marco