Left Join Group By in Script

[michaelsikora]

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;

[manoranjan_d]

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]

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 · Accepted solution]

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

[Anonymous]

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

[michaelsikora · Accepted solution]

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]

great

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