Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
q11hhg
Contributor III
Contributor III

sum and group by date in data load

Hi there , 

I just started to use Qliksense . I want to know how to group and aggregate the data in the data load editor. 

The attached screenshot is what I want to achieve:

q11hhg_0-1614696749190.png\

I used this script, but the result does not seem to be correct.

 

BNA_Login_Temp:

LOAD

"Customer_id lookUP"             AS "Customer ID",
Date("Date",'YYYY-MM-DD')      AS Date,
'1'                                                        AS "Total"

FROM [lib://AttachedFiles/BNA Logins.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate (APAC_Events)

BNA_Login:

Load
Date,
"Customer ID",
'Login'                                     AS "Event Type",
'BNA'                                        AS "Country",
Sum("Total")                         As "Total Events"

Resident BNA_Login_Temp
Group by "Customer ID",Date;
Drop Table BNA_Login_Temp;

  

Labels (2)
1 Solution

Accepted Solutions
duchezbr
Contributor III
Contributor III

I am thinking it is the concatenation.  Does the APAC_Events have a key that you can join on. I can get the desired output with the following but I don't know if this is feasible:

inner join(APAC_Events)
BNA_login:
Load
Dates,
Customer_id,
Sum(Total)
Resident BNA_login_temp
Group by Dates, Customer_id;

duchezbr_0-1614700095792.png

 

View solution in original post

4 Replies
duchezbr
Contributor III
Contributor III

You will get the desired result by removing 'Login' and 'BNA' when loading the BNA_Login table or add these fields to your Group by statement.

q11hhg
Contributor III
Contributor III
Author

Hi @duchezbr 

I need these two columns on my table.

it should look like this actually.

 

q11hhg_0-1614698896290.png

 

BUT when use the calculation 


count( {<
[Event Type] = {'Login'},
[Total Events]={">=2"}
>} distinct [Customer ID] )

 

it does not return the correct result, so I assuming that there is an error in my data load 

duchezbr
Contributor III
Contributor III

I am thinking it is the concatenation.  Does the APAC_Events have a key that you can join on. I can get the desired output with the following but I don't know if this is feasible:

inner join(APAC_Events)
BNA_login:
Load
Dates,
Customer_id,
Sum(Total)
Resident BNA_login_temp
Group by Dates, Customer_id;

duchezbr_0-1614700095792.png

 

q11hhg
Contributor III
Contributor III
Author

Hi @duchezbr  thank you, the two tables have exactly same column names, but no key that can let me to join them as far as I know.

Basiaclly they are data from two different regions and there is no key. I need to append/concatenate them