Left Join and count

Thu, 17 Dec 2009 00:59:54 GMT

Dear community,

I am doing a "left join" but it doesn't work as I excepted. Please have a look at the code and screenshot from the tables. I read the Help file and searched the forum, but since I am still not too familiar with all that functions of QV, I address this issue to you, too.

The problem seems to be located somewhere in table "Test5" as you can see on the image. There are no synth-keys in the script and the format of the column "DateHRS" is a timestamp ('dd.mm.yyyy hh'). Also there is the possibility of having more than one row per "DateHRS" in "Test 5" since you are having several IDs ("IDRZA"). The "count" function should generate a value ("CountX") based on other date (End1) that will be attached to all rows.

In the the "MAX table" I'd like to find the maximum value of "CountX" per "DateHRS"

Hope someone will have some minutes to check where the mistake is?

Test5:
Load
 DateHRS,
 MinKal
Resident Stundenkalender;

Left join (Test5)
Load
 DateHRS,
 Ala2,
 IDRZA,
 Mon,
 Weekd,
 Ala1,
 End1
Resident Test4;

LEFT JOIN (Test5)
LOAD
 Ala2,
 Count(distinct if(ISNULL(End1),'0',End1)) AS CountX
Resident Test3
Group by Ala2;

Drop table Test3;
Drop Table Test4;

MAX:
LOAD
 DateHRS,
 dual(time(floor(MinKal, 1/24),'hh:mm') & ' - ' & time(ceil (MinKal, 1/24),'hh:mm'), floor(frac(MinKal),1/24)) as Stunden
Resident Test5;

inner join
LOAD
 DateHRS,
 max(CountX) AS MAX
RESIDENT Test5
GROUP BY DateHRS;


Drop Table Stundenkalender;

Thanks for all comments,[:)]

Nico

Left Join and count

prieper — Thu, 17 Dec 2009 03:53:37 GMT

On a quick glance syntax might be o.k. Have you tried to run the script in parts and see the results in order to identify, where something obscure does happen? If the resident loads have the same number of fields than an existing table, you will not create a new table, hence also being unable to join.

Peter

topic Left Join and count in QlikView

Left Join and count

Left Join and count