How do a WHERE condition in a left join in Script Qlikview

Mon, 11 Oct 2010 10:28:44 GMT

Hello,

I want use a left join in a Script between to tables to have a classement of my results:

I explain I have a table of my result : STATS


Load

 [Business name],
 ChannelId,
 [Ending date] as "Date",
 num(WEEK([Ending date])) as "WeekendDate",
 num(YEAR([Ending date])) as "YearDate",
 KEY_USER,
 KEY_SITE,
 sport_trad as "sport",
 [Amount (EUR)] as "Result Amount (EUR)"
FROM SPORT.QVD

And a table with my groups of result with a Min and a Max of result Amount of the group.

Decile 1 Min 0.00 Euros Max 1.00 Euros


LOAD
 [Decile Year],
 [Decile Week],
 [Decile Date] as Date,
 Sport as sport
 Decile,
 MinDecile,
 MaxDecile
 FROM Decile (qvd);

I want per week a count of number of users in each Decile (group) and the average of Amount.

My problem it's I don'tg know do a left join per week to have this table because we don't have any link between Decile and STATS only amount and the number of the week.

I have try with this but it's not works 😞


LEFT JOIN (STATS)
LOAD
[Decile Year] as WeekendDate,
[Decile Week] as YearDate,
Sport assport,
Decile
RESIDENT DECILE
WHERE MinDecile <= " result Amount (EUR)" AND MaxDecile >= " result Amount (EUR)";

OR in the dashboard :


 count({$< MaxDecile={">=$(=sum([Result Amount (EUR)]))"},MinDecile={"<=$(=sum([Result Amount (EUR)]))"}>}KEY_USER)

Can you help me please??

Thanks a lot.

Bruno

topic How do a WHERE condition in a left join in Script Qlikview in QlikView

How do a WHERE condition in a left join in Script Qlikview