Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Restrictions in SUM / IF functions in Script

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 USERCHANNELNUMBER OF LOGIN
1INTERNET4
1MOBILE9
2INTERNET3
2MOBILE3
3INTERNET6
3MOBILE1
4INTERNET5
4MOBILE8
5INTERNET6
5MOBILE8
6INTERNET9
7INTERNET8
8INTERNET8
9INTERNET3
10INTERNET4
11MOBILE10
12MOBILE2
13MOBILE5

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 USERTOTAL_LOGINSLOGINS_INTERNETLOGINS_MOBILE
11349
2633
3761
41358
51468
6990
7880
8880
9330
10440
1110010
12202
13505

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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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;

View solution in original post

8 Replies
MK_QSL
MVP
MVP

attached

MK_QSL
MVP
MVP

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
MVP
MVP

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

Not applicable
Author

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
MVP
MVP

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
Partner - Creator III
Partner - Creator III

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

Not applicable
Author

That's perfect ! Thanks a lot Manish 🙂

Not applicable
Author

Thanks Philippe for your help !