Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Left Join Group By in Script

Hi Everyone,

I am working on making two different counts in my script so that I can find percent of utilization for each product. I was thinking that I could simply do a LEFT JOIN, followed by a GROUP BY.

The problem is that unless I have the same criteria in the table GROUP BY both tables, it doesn't work. Below is the script and attached is the file. Any advice is very appreciated.

starttbl:

LOAD

Date,

Dept,

Unit,

Count

FROM

LeftJoin.xls (biff, embedded labels, table is [Sheet1$]);

Reload:

Load

weekstart(Date) AS Week,

Dept,

Unit,

Sum(Count) AS Cnt2

RESIDENT

starttbl

GROUP BY

Dept, Unit, weekstart(Date);

LEFT JOIN (Reload)

LOAD

weekstart(Date) AS Week,

Dept,

Unit,

Sum(Count) AS Cnt

RESIDENT

starttbl

GROUP BY

Unit, weekstart(Date);

DROP TABLE starttbl;

2 Solutions

Accepted Solutions
Highlighted
Creator III
Creator III

Re: Left Join Group By in Script

if you want to group by unit and weekstart(date)  then remove the Dept in the left join load field & group by below is the script to achieve it


LEFT JOIN (Reload)

LOAD

weekstart(Date) AS Week,

Unit,

Sum(Count) AS Cnt

RESIDENT

starttbl

GROUP BY

Unit, weekstart(Date);

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Left Join Group By in Script

Thanks for help everyone. I figured out the answer that I was looking for:

starttbl:

LOAD

Date,

Dept,

Unit,

Count

FROM

LeftJoin.xls (biff, embedded labels, table is [Sheet1$]);

Reload:

Load

weekstart(Date) AS Week,

Dept,

Unit,

Sum(Count) AS Cnt2

RESIDENT

starttbl

GROUP BY

Dept, Unit, weekstart(Date);

LEFT JOIN (Reload)

LOAD

weekstart(Date) AS Week,

// Dept,

Unit,

Sum(Count) AS Cnt

RESIDENT

starttbl

GROUP BY

Unit, weekstart(Date);

DROP TABLE starttbl;

View solution in original post

6 Replies
Highlighted
Creator III
Creator III

Re: Left Join Group By in Script

you are missing Dept in the left join grp by

LEFT JOIN (Reload)

LOAD

weekstart(Date) AS Week,

Dept,

Unit,

Sum(Count) AS Cnt

RESIDENT

starttbl

GROUP BY

Unit, dept, weekstart(Date);

Highlighted
Contributor III
Contributor III

Re: Left Join Group By in Script

Manoranjan,

I am looking to have two different counts. One count grouped by unit, weekstart(date) and the other by Dept, Unit, weekstart(date).


Does it have to be grouped like you mentioned?


Thank You.

Highlighted
Creator III
Creator III

Re: Left Join Group By in Script

if you want to group by unit and weekstart(date)  then remove the Dept in the left join load field & group by below is the script to achieve it


LEFT JOIN (Reload)

LOAD

weekstart(Date) AS Week,

Unit,

Sum(Count) AS Cnt

RESIDENT

starttbl

GROUP BY

Unit, weekstart(Date);

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Left Join Group By in Script

kindly try;

starttbl:

LOAD

Date,

Dept,

Unit,

Count

FROM

LeftJoin.xls (biff, embedded labels, table is [Sheet1$]);

Reload:

Load

weekstart(Date) AS Week,

Dept,

Unit,

Sum(Count) AS Cnt2

RESIDENT starttbl GROUP BY Dept, Unit, weekstart(Date);

DROP TABLE starttbl;

LEFT JOIN (Reload)

LOAD

Week,

Dept,

Unit,

Sum(Count) AS Cnt

RESIDENT Reload GROUP BY Week,Unit, Dept;

Highlighted
Contributor III
Contributor III

Re: Left Join Group By in Script

Thanks for help everyone. I figured out the answer that I was looking for:

starttbl:

LOAD

Date,

Dept,

Unit,

Count

FROM

LeftJoin.xls (biff, embedded labels, table is [Sheet1$]);

Reload:

Load

weekstart(Date) AS Week,

Dept,

Unit,

Sum(Count) AS Cnt2

RESIDENT

starttbl

GROUP BY

Dept, Unit, weekstart(Date);

LEFT JOIN (Reload)

LOAD

weekstart(Date) AS Week,

// Dept,

Unit,

Sum(Count) AS Cnt

RESIDENT

starttbl

GROUP BY

Unit, weekstart(Date);

DROP TABLE starttbl;

View solution in original post

Highlighted
Creator III
Creator III

Re: Left Join Group By in Script

great

in left join load and group by if you remove it worked fine. hope so i given correct answer to you