topic Re: If problem in QlikView

If problem

Tue, 01 Apr 2014 10:48:35 GMT

Hi,

2 weeks old into QV, and trying to get on board with the basics.

I have data in columns, SalesID, LA, LP, VO. There are basically 2 values on columns LA,LP, VO, either 1 or 0. I would like to create a 5th column that checks that if there is 0 on any of the columns in the row, then the new row value is 1, otherwise it is 0.

if((LAr or VO or LP) = 0, 0, 1) as VR. This however doesn't work.

After this would like to create 4 new tables.

SalesID, LA =1

SalesID, VO =

SalesID, LP =1

SalesID, VR =1

Help on this much appreciated...

Re: If problem

MK_QSL — Tue, 01 Apr 2014 10:50:38 GMT

Try

IF(LA&LP&VO = 000, 0, 1) as VR

UPDATE : My mistake in previous reply. I overlooked your requirements. I thought you want VR = 0 if all LA, LP and VO are 0...

you can use below...

Temp:

Load * Inline

[

SalesID, LA, LP, VO

A, 0,0,1

B, 0,1,0

C, 0,0,1

D, 0,0,0

E, 0,1,1

F, 1,1,1

];

NoConcatenate

FINAL:

Load

SalesID,

IF(LA= 0 or LP = 0 or VO = 0,0,1) as VR

Resident Temp;

Re: If problem

alexandros17 — Tue, 01 Apr 2014 10:51:14 GMT

the sysntax is:

if(LAr=0 or VO=0 or LP=0, 0, 1) as VR

Re: If problem

its_anandrjs — Tue, 01 Apr 2014 10:57:03 GMT

Write like

If ( LA = 0 OR VO = 0 OR LP = 0, 1, 0) as VR

Hope this helps

Re: If problem

Tue, 01 Apr 2014 10:57:40 GMT

cheers but this didn't work since I was looking that if any of the columns have a different value and not that if all of them have 0 values.

Re: If problem

Tue, 01 Apr 2014 10:58:07 GMT

Thanks. Works like a charm

Re: If problem

MK_QSL — Tue, 01 Apr 2014 10:58:35 GMT

Check my second solution...

Re: If problem

its_anandrjs — Tue, 01 Apr 2014 11:01:27 GMT

Or you can add this in your load statement like

If ( (LA = 0 OR VO = 0 OR LP = 0), 1, 0) as VR

Hope this helps

Re: If problem

Tue, 01 Apr 2014 11:39:21 GMT

Hi again,

The above was a 2 part question, sorry for the unclarity. So part 2, how would I then create for tables where:

SalesID, LA = only 1

SalesID, VO =only 1

SalesID, LP =only 1

SalesID, VR =only 1

Re: If problem

Tue, 01 Apr 2014 11:39:44 GMT

both work Anand. cheers

Re: If problem

its_anandrjs — Tue, 01 Apr 2014 11:46:22 GMT

If got correct so mark my thread as correct so refer it as correct

Thanks & Regards

Re: If problem

its_anandrjs — Tue, 01 Apr 2014 11:47:47 GMT

Change expression like

If ( LA = 0 OR VO = 0 OR LP = 0, 0, 1) as VR

Thanks & Regards

Re: If problem

Tue, 01 Apr 2014 12:22:53 GMT

For creating 4 tables, take straight table from charts,

in that take 2 dimensions: 1) sales ID and 2) calculated dimension- =if(LA=1,LA,null())

and for this expression check box "suppress null values"

and in expression write down "1" and in Presentation tab select 1 and click on hide expression column

U will get the result and do the same for the rest.