topic Left Join issue in QlikView

Left Join issue

Sun, 08 Jul 2012 08:13:24 GMT

I am trying to calculate sub-total for a number of dimensions as Brand, FA, Submision, Year, Month and then to add this subtotal to original table KEYS_TEMP by applying left join. Something is not right. Could you please advise which part of the below script is wrong?

KEYS_TEMP:

CrossTable(Month, Amount, 5)

LOAD @1 as Brand,

$(Temp_Year) as Year,

'$(vSheet)' as GCC,

'A&P' as FA,

'Actual' as Submission,

@4 as '1',

@5 as '2',

@6 as '3',

@7 as '4',

@8 as '5',

@9 as '6',

@10 as '7',

@11 as '8',

@12 as '9',

@13 as '10',

@14 as '11',

@15 as '12'

FROM $(File)

(biff, no labels, header is 1 lines, table is $(vSheet)$);

KEYS_AGGR:

LOAD

Brand,

Year,

GCC,

FA,

Submission,

Month,

sum(Amount) as Total_Amount

RESIDENT KEYS_TEMP

Group BY Brand, Year, GCC, FA, Submission, Month;

LEFT JOIN (KEYS_TEMP)

LOAD

Brand,

Year,

GCC,

FA,

Submission,

Month,

Total_Amount

Resident KEYS_AGGR;

Re: Left Join issue

pljsoftware — Sun, 08 Jul 2012 10:29:58 GMT

Hi Badlam,

if you send an example is better for an help.

Your script generate an synthetic key, I think that you must to create a unique key for exemple

Brand & GCC & FA & Submission & Year & Month as Key

so you can make this

KEYS_AGGR:

LOAD

Key,

sum(Amount) as Total_Amount

RESIDENT KEYS_TEMP

Group BY Key;

and delete the Left Join.

I hope this help you.

Regards

Luca Jonathan Panetta

PLJ Software

Re: Left Join issue

jonathandienst — Sun, 08 Jul 2012 15:48:16 GMT

Please explain exactly what is wrong. Are you getting an error, if so, please give details. If it is not failing with an error, please explain what is wrong with the result. Based on the information you have provided, I can only take wild guesses.

It also helps if you upload a sample model illustrating the problem. You are far more likely to get help that way.

Regards

Jonathan