Restrictions in SUM / IF functions in Script

[#1]

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:

LOGIN:

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

or

   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

[MK_QSL]

attached

[MK_QSL]

LOGIN:

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];

[MK_QSL]

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

[#5]

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.

[MK_QSL · Accepted solution]

LOGIN:

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;

[pgrenier]

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

LOGIN:

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

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

[#8]

That's perfect ! Thanks a lot Manish 🙂

[#9]

Thanks Philippe for your help !