topic Re: If and Else Logic on backend in QlikView

If and Else Logic on backend

er_mohit — Mon, 13 Oct 2014 04:50:54 GMT

Hi ,

I have a data like below.

Year:

LOAD

* Inline [

Month,Year

1,2014

2,2014

3,2014

4,2014

5,2014

6,2014

7,2014

8,2014

1,2013

2,2013

3,2013

4,2013

5,2013

6,2013

7,2013

8,2013

9,2013

10,2013

11,2013

12,2013

];

If User select Year = 2013

i want an if condition logic that

In above i have 1 to 12 minth for Year = 2013 and for Year = 2014 Month is 1 to 8

I want to create a dimension based on MaxMonth group by year.

Compare that MaxMonth with given Month group by Year.

So, what i want the logic made like below

So, my logic is if Month = 1,'Jan',

Month<=2,'Jan & Feb',

Month>=1 and Month <=3,'Q1',

Month<=4 ,'Apr',

Month>=4 and Month<=5,'Apr & May',

Month>=1 and Month<=6,H1,

Month=7,July,

Month>=7 and Month<=8 ,'July & Aug',

Month> = 7 and Month<=9,'Q3',

Month=10,'Oct',

Month>=10 and Month<=11,'Oct and Nov',

Month>=10 and Month<=12,'Q4',

Month>6 and Month<=12,'H2',

Month>=1 and Month<=12,'YTD Dec' as NewDimension

here the static value m write is MaxMonth of that particular Year.

My Output:

So, for Year =2013

The Value having in NewDimension field is

YTD Dec

if select Year = 2014

then in NewDimension field having value alike

July & Aug

YTD Aug

Any help will be appreciable.

Re: If and Else Logic on backend

anbu1984 — Mon, 13 Oct 2014 06:04:32 GMT

Year:

LOAD

* Inline [

Month,Year

1,2014

2,2014

3,2014

4,2014

5,2014

6,2014

7,2014

8,2014

1,2013

2,2013

3,2013

4,2013

5,2013

6,2013

7,2013

8,2013

9,2013

10,2013

11,2013

12,2013

];

NoConcatenate

MaxMonth:

Load Year,SubField(NewDim,',',IterNo()) While IterNo() <= SubStringCount(NewDim,',')+1;

Load Year, If(MthSum=12,'Q1,Q2,H1,Q3,Q4,H2,YTD Dec',If(MthSum=11,'Q1,Q2,H1,Q3,Oct and Nov,YTD Nov',If(MthSum=8,'Q1,Q2,H1,July & Aug,YTD Aug'))) As NewDim;

//EDIT

Load Sum(Month) As MthSum, Year Resident Year Group by Year;

Re: If and Else Logic on backend

er_mohit — Mon, 13 Oct 2014 06:21:26 GMT

Hi anbu1984 ,

Thanks for you reply

I m checking your output but it doesn't link with Month values. like for Year = 2014

if m select

Q1 then it 'll link to Month field values 1 to 3

Q2 then it 'll link to Month field values 4 to 6

H1 then it 'll link to Month field values 1 to 6

July & Aug then it 'll link to Month field values 7 to 8

YTD Aug then it 'll link to Month foeld values 1 to 8.

Hope you understand. and same relevant for Year = 2014

Here above defined logic have mistake for Apr the line is Month=4 ,'Apr'.

Regards

Mohit

Re: If and Else Logic on backend

anbu1984 — Mon, 13 Oct 2014 06:28:17 GMT

Create another table like below and link this table to newly created dimension

Load * Inline [

NewDim,Month

Q1,1

Q1,2

Q1,3

Q2,4

Jul & Aug,7

Jul & Aug,8

...

YTD Jan,1

YTD Feb,1

YTD Feb,2

...];

Re: If and Else Logic on backend

er_mohit — Mon, 13 Oct 2014 09:26:57 GMT

Thanks for reply back.

If my Year table also having the Date field then ?

Regards

Mohit

Re: If and Else Logic on backend

anbu1984 — Mon, 13 Oct 2014 09:36:33 GMT

Not getting your question

Re: If and Else Logic on backend

er_mohit — Mon, 13 Oct 2014 10:05:23 GMT

The same logic i applied using master calendar but can't get success.

Please find attachment.

Regards

Mohit

Re: If and Else Logic on backend

er_mohit — Tue, 14 Oct 2014 04:12:11 GMT

Please help me any suggestion

Re: If and Else Logic on backend

anbu1984 — Tue, 14 Oct 2014 05:49:51 GMT

MaxMonth table is empty. Can you reload and check.

Re: If and Else Logic on backend

er_mohit — Tue, 14 Oct 2014 06:23:49 GMT

Please find attachment, Here the issue is MthSum is consolidate all value instead of separately as alike your above example. !

Regards

Mohit

Re: If and Else Logic on backend

anbu1984 — Tue, 14 Oct 2014 06:31:11 GMT

Try this

Load Count( DISTINCT Month ) As MthSum, Year Resident MasterCalendar Group by Year;

Re: If and Else Logic on backend

er_mohit — Tue, 14 Oct 2014 06:55:20 GMT

Ok. Thanks for your reply.

i have tried it but the problem is it shows single value rather than consolidate data of month value as you suggested in above example for creating logic.

Please share the application demo if you have done it.

Regards

Mohit

Re: Re: If and Else Logic on backend

anbu1984 — Tue, 14 Oct 2014 07:44:01 GMT

Check this