topic Re: group by with Resident table in QlikView

group by with Resident table

ilanbaruch — Tue, 03 Nov 2015 15:10:48 GMT

hi all,

I want to create a new field based on two tables.

during script load, first table created (temp_TABLE) but when second table starts (new_table) the script fails

also tried it with Concatenate.

temp_TABLE:
LOAD
user_id,
sales_amount,
order_id

Resident table1;

Left Join (temp_TABLE)
LOAD
Distinct
user_id,
Department
Resident
table2;

new_table:
LOAD

Department,
Sum(sales_amount)/Count(order_id) as avg_order
Resident temp_TABLE
Group By Department;

DROP Table temp_TABLE;

Re: group by with Resident table

swuehl — Tue, 03 Nov 2015 15:24:38 GMT

What is the error message? Could you post the document log?

Re: group by with Resident table

Anonymous — Tue, 03 Nov 2015 15:37:26 GMT

What error are you receiving?

Absent that detail, try the final load with the ratio broken into separate sum & count columns. If your join is failing for some reason, you might be seeing a divide-by-zero error because of your Group By clause.

Also, watch out for the use of "distinct" with join statements.

QlikView Blog Q-Tip #1 - Beware of DISTINCT! | Natural Synergies

Re: group by with Resident table

settu_periasamy — Tue, 03 Nov 2015 15:53:52 GMT

Hi,

May be try to use NOCONCATENATE before newtable..

Noconcatenate

Newtable:

Re: group by with Resident table

Tue, 03 Nov 2015 16:23:36 GMT

This is because auto concatenate, try this:

NoConcatenate

temp_TABLE:

LOAD

user_id,

sales_amount,

order_id

Resident table1;

Left Join (temp_TABLE)

LOAD

user_id,

Department

Resident

table2;

Re: group by with Resident table

Gabriel — Tue, 03 Nov 2015 16:27:56 GMT

Hi,

You have no temp_TABLE created. You need NoConcatenate before temp_TABLE