topic Re: If statement where buckets may overlap in QlikView

If statement where buckets may overlap

Fri, 10 Jun 2016 13:34:23 GMT

CUS ID	CC	Sav	Cur
1	1	1	1
2	1	0	0
3	0	0	1
4	0	1	1
5	0	1	1
6	0	1	0
7	0	1	0
8	0	1	0
9	0	1	1

Logic is something like,

if CC = 1 then 'Cards',

if Sav = 1 and Cur = 1, 'Marked'

Please can you help with the correct IF statement for this. Some CUS ID's should end up with 2 values; ie they fall into both the Cards and Marked bucket

Thanks

Stu

Re: If statement where buckets may overlap

swuehl — Fri, 10 Jun 2016 13:37:12 GMT

You need to create a link table, like

LOAD [CUS ID]

'Cards' as NewField

RESIDENT YourTable

WHERE CC =1;

CONCATENATE

LOAD [CUS ID]

'Marked' as NewField

RESIDENT YourTable

WHERE Sav=1 AND Cur =1;

Re: If statement where buckets may overlap

sunny_talwar — Fri, 10 Jun 2016 13:38:50 GMT

May be like this:

If(CC = 1 and Sav = 1 and Cur = 1, 'Both',

If(CC = 1, 'Cards', If(Sav = 1 and Cur = 1, 'Marked')))

Re: If statement where buckets may overlap

sunny_talwar — Fri, 10 Jun 2016 13:41:26 GMT

Something like this?

Re: If statement where buckets may overlap

florentina_doga — Fri, 10 Jun 2016 13:44:50 GMT

use this

load *,

if(len(cards)<>0 and len(marked),1,0) as flag;

load *,

if( CC = 1, 'Cards') as cards,

if (Sav = 1 and Cur = 1, 'Marked') as marked

;

load * inline [CUSID, CC, Sav, Cur

1, 1, 1, 1,

2, 1, 0, 0,

3, 0, 0, 1,

4, 0, 1, 1,

5, 0, 1, 1,

6, 0, 1, 0,

7, 0, 1, 0,

8, 0, 1, 0,

9, 0, 1, 1];

flag is 1 for not empty cards and not empty marked

Re: If statement where buckets may overlap

Fri, 10 Jun 2016 13:56:17 GMT

thanks

Re: If statement where buckets may overlap

Fri, 10 Jun 2016 13:57:10 GMT

Hi Sunny, thanks for the help but i needed multiple values for those Customers who appear more than once

Thanks for helping anyway.

Re: If statement where buckets may overlap

sunny_talwar — Fri, 10 Jun 2016 14:01:38 GMT

Sounds good