topic How to consider only the full Steps values of entity (Id) ? in QlikView

How to consider only the full Steps values of entity (Id) ?

yanivvl0 — Thu, 15 Sep 2016 08:51:53 GMT

hi to all ,

we have in our model Id's , each of them can reach the steps 1 or 1+2 or 1+2+3 or 1+2+3+4 . ( step 4 is the finsh ).

a step is not done Unless there is the next step for the Id. ( excep step 4 ).

for each step there is a value.

we need to sum just steps ended ( excep step 4 ) , for example : if an Id have 1,2,3 steps - the sum is for the values of steps 1,2

not 3 .

take a look :

the total sum is 2440 BUT it shuld be ( by is calculate ? ) 1600 .

So , how can we do that ? need your great help ..

another one : how can we sum the values of step 1 - only if there is step 2 ?

Thanks!

Re: How to consider only the full Steps values of entity (Id) ?

Anonymous — Thu, 15 Sep 2016 09:10:12 GMT

go with Prevous() function on the column "is calcluate"?+

and sum it up..

Re: How to consider only the full Steps values of entity (Id) ?

sunny_talwar — Thu, 15 Sep 2016 09:17:25 GMT

May be this:

=Sum({<Key = {"=Aggr(Max(TOTAL <Id> Step), Id, Step) = 4 or Aggr(Max(TOTAL <Id> Step), Id, Step) > Step"}>}Value)

Where Key is created in script like this:

Table:

LOAD AutoNumber(Id&Step) as Key,

LOAD * INLINE [

Id, Step, Value

10, 1, 100

10, 2, 200

10, 3, 40

20, 1, 300

30, 1, 50

30, 2, 150

30, 3, 300

30, 4, 100

40, 1, 200

40, 2, 400

50, 1, 500

50, 2, 100

];

Re: How to consider only the full Steps values of entity (Id) ?

Anonymous — Thu, 15 Sep 2016 10:25:04 GMT

Hi Yaniv,

You can calculate it on script:

Data:
LOAD * INLINE [
    Id, Step, Value
    10, 1, 100
    10, 2, 200
    10, 3, 40
    20, 1, 300
    30, 1, 50
    30, 2, 150
    30, 3, 300
    30, 4, 100
    40, 1, 200
    40, 2, 400
    50, 1, 500
    50, 2, 100
];

LEFT JOIN (Data)
LOAD
    Id,
    Count(DISTINCT Step) as CountSteps,
    MAX(Step)             as MaxStep
RESIDENT Data
Group By Id;

Aux:
LOAD *,
    If(Step = MaxStep,If(CountSteps = 4,'Y','N'),'Y') as Calculate;
LOAD *
RESIDENT Data;

DROP TABLE Data;

Then, your expression is more simple: Sum({<Calculate={'Y'}>} Value)

If you want to aggregate steps value (two examples, all values and values by Id), then calculate Aux table like this:

//Aux table for aggregate by Id steps value
Aux:
LOAD *,
     IF(Peek(Id) = Id,
         If(Calculate = 'Y',Value+Peek(AggrValue),Peek(AggrValue)),Value) as AggrValue;
LOAD *,
    If(Step = MaxStep,If(CountSteps = 4,'Y','N'),'Y') as Calculate;
LOAD *
RESIDENT Data
ORDER BY Id asc, Step asc;

//Aux table for aggregate all calculated steps values
Aux:
LOAD *,
     IF(IsNull(Peek(Id)),Value,
         If(Calculate = 'Y',Value+Peek(AggrValue),Peek(AggrValue))) as AggrValue;
LOAD *,
    If(Step = MaxStep,If(CountSteps = 4,'Y','N'),'Y') as Calculate;
LOAD *
RESIDENT Data
ORDER BY Id asc, Step asc;

Regards!

Re: How to consider only the full Steps values of entity (Id) ?

yanivvl0 — Sun, 18 Sep 2016 08:18:01 GMT

thanks Sunny - much help again !!

Re: How to consider only the full Steps values of entity (Id) ?

yanivvl0 — Sun, 18 Sep 2016 08:19:03 GMT

hello Manuel - thank you for helping us !!