topic Re: Restrictions in SUM / IF functions in Script in QlikView

Restrictions in SUM / IF functions in Script

Thu, 30 Jan 2014 15:33:43 GMT

Hi all,

I'm trying to add two new columns in a table by using the script, but unfortunately, I don't manage to rise it. My situation is the following:

My main table is:

ID USER	CHANNEL	NUMBER OF LOGIN
1	INTERNET	4
1	MOBILE	9
2	INTERNET	3
2	MOBILE	3
3	INTERNET	6
3	MOBILE	1
4	INTERNET	5
4	MOBILE	8
5	INTERNET	6
5	MOBILE	8
6	INTERNET	9
7	INTERNET	8
8	INTERNET	8
9	INTERNET	3
10	INTERNET	4
11	MOBILE	10
12	MOBILE	2
13	MOBILE	5

My script:

LOAD [ID USER],

CHANNEL,

[NUMBER OF LOGIN]

FROM

[PRUEBA_1.xlsx]

(ooxml, embedded labels);

TOTALLOGIN:

LOAD [ID USER],

SUM([NUMBER OF LOGIN]) AS TOTAL_LOGINS

Resident LOGIN

Group by [ID USER];

What I'd like to obtain:

ID USER	TOTAL_LOGINS	LOGINS_INTERNET	LOGINS_MOBILE
1	13	4	9
2	6	3	3
3	7	6	1
4	13	5	8
5	14	6	8
6	9	9	0
7	8	8	0
8	8	8	0
9	3	3	0
10	4	4	0
11	10	0	10
12	2	0	2
13	5	0	5

I already managed to get the first column thanks to the Qlik Community. However, to get the columns LOGINS_INTERNET and LOGINS_MOBILE, the following sentences haven't work:

if(CHANNEL='INTERNET', [NUMBER OF LOGIN], '0') AS LOGINS_INTERNET,

if(CHANNEL='MOBILE', [NUMBER OF LOGIN], '0') AS LOGINS_MOBILE

SUM({<CHANNEL={'INTERNET'}>}[NUMBER OF LOGIN]) AS LOGINS_INTERNET,

SUM({<CHANNEL={'MOBILE'}>}[NUMBER OF LOGIN]) AS LOGINS_MOBILE,

Could you tell me where I'm mistaking?

Thanks a lot in advance for your help.

Regards,

Vincent

Re: Restrictions in SUM / IF functions in Script

MK_QSL — Thu, 30 Jan 2014 16:03:24 GMT

attached

Re: Restrictions in SUM / IF functions in Script

MK_QSL — Thu, 30 Jan 2014 16:04:06 GMT

LOAD [ID USER],

CHANNEL,

[NUMBER OF LOGIN]

FROM

[PRUEBA_1.xlsx]

(ooxml, embedded labels);

TOTALLOGIN:

LOAD [ID USER],

SUM([NUMBER OF LOGIN]) AS TOTAL_LOGINS

Resident LOGIN

Group by [ID USER];

INTERNET:

LOAD [ID USER],

SUM([NUMBER OF LOGIN]) AS TOTAL_LOGINS_INTERNET

Resident LOGIN

Where CHANNEL = 'INTERNET'

Group by [ID USER];

MOBILE:

LOAD [ID USER],

SUM([NUMBER OF LOGIN]) AS TOTAL_LOGINS_MOBILE

Resident LOGIN

Where CHANNEL = 'MOBILE'

Group by [ID USER];

Re: Restrictions in SUM / IF functions in Script

MK_QSL — Thu, 30 Jan 2014 16:11:01 GMT

Also, the same can be achieved without creating an extra field in script...

Create a straight table

Dimension = ID USER

Expression 1 = SUM([NUMBER OF LOGIN]) //This will give you total number of LOGIN

Expression 2 = SUM({<CHANNEL = {'INTERNET'}>}[NUMBER OF LOGIN]) // This will give you total login where channel is Internet

Expression 3 = SUM({<CHANNEL = {'MOBILE'}>}[NUMBER OF LOGIN]) // This will give you total login where channel is Mobile

Re: Restrictions in SUM / IF functions in Script

Thu, 30 Jan 2014 16:15:07 GMT

Hi Manish,

Thanks a lot for your help. However, I've just one more question: is it possible to have one single table instead of 3? (TOTALLOGIN, INTERNET, MOBILE). I need to create one single table through the script.

Thanks a lot again and sorry for inconveniences.

Re: Restrictions in SUM / IF functions in Script

MK_QSL — Thu, 30 Jan 2014 16:17:02 GMT

LOAD [ID USER],

CHANNEL,

[NUMBER OF LOGIN]

FROM

[PRUEBA_1.xlsx]

(ooxml, embedded labels);

TOTALLOGIN:

LOAD [ID USER],

SUM([NUMBER OF LOGIN]) AS TOTAL_LOGINS

Resident LOGIN

Group by [ID USER];

INTERNET:

LOAD [ID USER],

SUM([NUMBER OF LOGIN]) AS TOTAL_LOGINS_INTERNET

Resident LOGIN

Where CHANNEL = 'INTERNET'

Group by [ID USER];

MOBILE:

LOAD [ID USER],

SUM([NUMBER OF LOGIN]) AS TOTAL_LOGINS_MOBILE

Resident LOGIN

Where CHANNEL = 'MOBILE'

Group by [ID USER];

LEFT JOIN (LOGIN) Load * Resident TOTALLOGIN;

LEFT JOIN (LOGIN) Load * Resident INTERNET;

LEFT JOIN (LOGIN) Load * Resident MOBILE;

Drop Tables TOTALLOGIN, INTERNET, MOBILE;

Re: Restrictions in SUM / IF functions in Script

pgrenier — Thu, 30 Jan 2014 16:21:11 GMT

Hello Vincent,

You don't actually need to precalculate the totals in your load script, as you can obtain the result in your Straight Table chart quite easily using the following three expressions:

Internet Logins: Sum({<CHANNEL={"INTERNET"}>} [NUMBER OF LOGIN])

Mobile Logins: Sum({<CHANNEL={"MOBILE"}>} [NUMBER OF LOGIN])

Total Logins: Sum([NUMBER OF LOGIN])

Regards,

Philippe

P.S. See the attached PRUEBA_1a.qvw file with the integrated example expressions, and the script limited to

LOAD [ID USER], CHANNEL, [NUMBER OF LOGIN]

FROM [PRUEBA_1.xlsx] (ooxml, embedded labels);

Re: Restrictions in SUM / IF functions in Script

Thu, 30 Jan 2014 16:24:18 GMT

That's perfect ! Thanks a lot Manish 🙂

Re: Restrictions in SUM / IF functions in Script

Thu, 30 Jan 2014 16:24:42 GMT

Thanks Philippe for your help !