topic Re: Set Analysis for Nested loop in QlikView

Set Analysis for Nested loop

alkesh_sharma — Tue, 18 Feb 2014 08:53:38 GMT

Hello,

Can anybody please tell me the set analysis statement for nested loop.

Re: Set Analysis for Nested loop

sujeetsingh — Tue, 18 Feb 2014 08:56:47 GMT

Yup

SUM(if(Brand='LEVIS',QTY)) now for this statement you can write set expression

sum({$<Brand={'LEVIS'} >} QTY)

This just for reference you can similarly go for the nested if

Re: Set Analysis for Nested loop

Tue, 18 Feb 2014 09:21:29 GMT

What do you mean by 'loop'?

Re: Set Analysis for Nested loop

alkesh_sharma — Tue, 18 Feb 2014 10:03:03 GMT

Thankyou Sujeet for the quick response, It would be great if you have help me out a lil in detail.

I need the exact Set analysis statement for the below nested if:

If([Rolling Month] = 'Apr - 2005',1,

If([Rolling Month] = 'May - 2005',2,

If([Rolling Month] = 'Jun - 2005',3,

If([Rolling Month] = 'Jul - 2005',4,

If([Rolling Month] = 'Aug - 2005',5,

If([Rolling Month] = 'Mar - 2010',60,))))))

Re: Set Analysis for Nested loop

Tue, 18 Feb 2014 10:05:37 GMT

Add a column with 1, 2, 3 .... for each Rolling Month

and use it

Fabrice

Re: Set Analysis for Nested loop

alkesh_sharma — Tue, 18 Feb 2014 10:09:06 GMT

Thankyou but this will not solve my problem. I would need to write 60 rolling month as per your answer.

Re: Set Analysis for Nested loop

alkesh_sharma — Tue, 18 Feb 2014 10:13:57 GMT

I need the exact Set analysis statement for the below nested if:

If([Rolling Month] = 'Apr - 2005',1,

If([Rolling Month] = 'May - 2005',2,

If([Rolling Month] = 'Jun - 2005',3,

If([Rolling Month] = 'Jul - 2005',4,

If([Rolling Month] = 'Aug - 2005',5,

If([Rolling Month] = 'Mar - 2010',60,))))))

/message/470575?et=watches.email.thread

Re: Set Analysis for Nested loop

Tue, 18 Feb 2014 10:18:51 GMT

With this column (or any expression that returns 1 to 60 for the rolling months), you avoid all these nested if()

What do you want to do ?

1) to do a nested complicated thing whatever the result ?

2) return a Number for a given Rolling Month ? and insert it into another expression ? use it as a dimension ?

Fabrice

Re: Set Analysis for Nested loop

alkesh_sharma — Tue, 18 Feb 2014 10:37:45 GMT

I want to use it as a dimension. creating a column is a hectic task but would definitely solve the purpose. I am looking for something that would reduce the length of script.

Re: Set Analysis for Nested loop

Tue, 18 Feb 2014 10:43:44 GMT

in the script, use functions like

rowno(), autonumber(), autonumberhash128 or 256()

in the UI (Calculated Dimension), you can use also a function like FieldIndex('Field', Field) to get sth numbered from 1 to the number of values (according to the FIRST load order).

Fabrice

Re: Set Analysis for Nested loop

jerem1234 — Tue, 18 Feb 2014 20:47:15 GMT

Here try this:

=mod(month(date#([Rolling Month], 'MMM - YYYY'))-4, 12)+1+(Year(addmonths(date#([Rolling Month], 'MMM - YYYY'),-3))-2005)*12

You can use this as calculated dimension or in script. You might also be able to condense this if certain fields exist and are in date format.

You might even be able to sort by the date and just use something like rowno() in the script. The way you go about it depends on how your data looks.

Hope this helps!