topic Re: Left Join Group By in Script in QlikView

Left Join Group By in Script

michaelsikora — Tue, 11 Sep 2018 12:14:44 GMT

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;

Re: Left Join Group By in Script

manoranjan_d — Tue, 11 Sep 2018 12:23:06 GMT

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

Re: Left Join Group By in Script

michaelsikora — Tue, 11 Sep 2018 13:23:47 GMT

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.

Re: Left Join Group By in Script

manoranjan_d — Tue, 11 Sep 2018 13:42:05 GMT

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

Re: Left Join Group By in Script

Anonymous — Tue, 11 Sep 2018 15:55:41 GMT

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;

LeftJoin2.qvw

Re: Left Join Group By in Script

michaelsikora — Tue, 11 Sep 2018 20:00:40 GMT

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;

Re: Left Join Group By in Script

manoranjan_d — Wed, 12 Sep 2018 12:42:41 GMT

great

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