Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
neena123
Partner
Partner

Need help using Group BY with data from an OLE DB connection

Hello! I need some help. Never worked with an OLE DB source before and I am having trouble understanding how to do a Group By. I am trying to calculate how many packages is shipped per user by hour. I am trying to group the fields Username and Date so that it is unique and I can then count how many packages were shipped by hour. I tried to pull the data in one table but it did not like that. I tried to do a join then a resident table and use Group By but it didn't like that either. Is it because the data is from two different Data sources? 

//Ship Logs***************************************************************************************************************************
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=connectship;Data Source=THM-COM-r01sm;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BRB-TPM-SMRT01;Use Encryption for Data=False;Tag with column collation when possible=False] //************************************************************************************************************************************
Ship:
LOAD
Date_Time AS Date,
Time#(right(Date_Time,12),'HH:MM:SS') AS Time,
Mid(Date_Time,10,2) AS DateHour,
Invoice_Number AS Invoice_Number
;
SQL SELECT "Date_Time","Invoice_Number"
FROM "CSW_Logs".dbo."Ship_Log";

//Packages****************************************************************************************************************************
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=connectship;Data Source=THM-COM-r01sm;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID= BRB-TPM-SMRT01;Use Encryption for Data=False;Tag with column collation when possible=False] //************************************************************************************************************************************
LEFT JOIN
LOAD
reference_1 as Invoice_Number,
reference_10 as Username,
reference_2 as Client,
user as Facility;
SQL SELECT "reference_1","reference_10","reference_2","user"
FROM connectship.dbo.packages;

 

Labels (5)
1 Solution

Accepted Solutions
rwunderlich
Luminary Alumni
Luminary Alumni

It seems to me that the left join you posted should work as the first step.  BTW, it's a good idea to specify the target table in the join like this:

LEFT JOIN (Ship)

Then you should be able to create a new table from the Resident Ship.

ShipCount:
LOAD count(Invoice_Number) as ShipCount,
username,
DateHour
Resident Ship
Group by username, DateHour;

-Rob

View solution in original post

3 Replies
rwunderlich
Luminary Alumni
Luminary Alumni

Is there only 1 Ship per Invoice Number?  Or is there sometimes multiple Ship rows per Invoice Number?

-Rob

neena123
Partner
Partner
Author

Hello! There is only 1 ship per invoice 

rwunderlich
Luminary Alumni
Luminary Alumni

It seems to me that the left join you posted should work as the first step.  BTW, it's a good idea to specify the target table in the join like this:

LEFT JOIN (Ship)

Then you should be able to create a new table from the Resident Ship.

ShipCount:
LOAD count(Invoice_Number) as ShipCount,
username,
DateHour
Resident Ship
Group by username, DateHour;

-Rob

View solution in original post