group by with Resident table

[ilanbaruch]

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;

[swuehl]

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

[Anonymous]

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

[settu_periasamy]

Hi,

May be try to use NOCONCATENATE before newtable..

Noconcatenate

Newtable:

[#5 · Accepted solution]

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;

[Gabriel]

Hi,

You have no temp_TABLE created. You need NoConcatenate before temp_TABLE