Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
michaelsikora
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
manoranjan_d
Specialist
Specialist

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

michaelsikora
Contributor III
Contributor III
Author

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
manoranjan_d
Specialist
Specialist

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

michaelsikora
Contributor III
Contributor III
Author

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.

manoranjan_d
Specialist
Specialist

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

yogarajsubraman
Contributor III
Contributor III

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;

michaelsikora
Contributor III
Contributor III
Author

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;

manoranjan_d
Specialist
Specialist

great

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