topic LEFT JOIN and RESIDENT in QlikView

LEFT JOIN and RESIDENT

Thu, 15 Sep 2011 15:12:30 GMT

Hello everybody,

I have 2 tables I want to join to filter information but i'm not arrived.

Tab1:

Date_Calendrier,

Jour,

Mois,

Annee,

Ferie;

Tab2:

NB_Heure1,

NB_Heure2,

Date_Temp,

Individu

And I want

Tab3:

Load

Individu,

Date_Temp,

if(notnull(Tab1.Ferie),0,NB_Heure1+NB_Heure2) as NB_Heure3

Resident Tab2

where Date_Temp=Date_Calendrier

How I can my Tab 3???Have you any idea please???

Thank you for tyour help!

Lna

LEFT JOIN and RESIDENT

its_anandrjs — Thu, 15 Sep 2011 15:35:35 GMT

Hi,

I suggest you need to use isnull( ) function

and need to change in code some thing like below

Tab1:

Date_Calendrier,

Jour,

Mois,

Annee,

Ferie;

Tab2:

NB_Heure1,

NB_Heure2,

Date_Temp,

Individu

Tab3:

Load

Individu,

Date_Temp,

if(isnull(Tab1.Ferie)>0,NB_Heure1+NB_Heure2) as NB_Heure3

Resident Tab2

where Date_Temp=Date_Calendrier

Let me know

HTH

Rgds

Anand

LEFT JOIN and RESIDENT

Thu, 15 Sep 2011 15:40:45 GMT

Hi Anand,

Thank you for your help but The problem is the Where condition.

the error message it's Field Date_Calendrier unkow...

Have you any idea?

LEFT JOIN and RESIDENT

SunilChauhan — Thu, 15 Sep 2011 15:48:29 GMT

try this

where Date_Temp=Tab1..Date_Calendrier

else share the sample

Re: LEFT JOIN and RESIDENT

its_anandrjs — Thu, 15 Sep 2011 15:52:23 GMT

Hi,

Check the date format of the two date field

Date_Temp = Date_Calendrier is same or not and i think naming convention please check once there is some small mistake i think.

Naming convention like Qualify or not

like Tab1, Tab2

When ever you use Qualify please check if it is some think like

Tablename1.Datefield1 = Tablename2.Datefield2

HTH

Rgds

Anand

Re: LEFT JOIN and RESIDENT

its_anandrjs — Thu, 15 Sep 2011 15:56:50 GMT

Hi,

If it is Qualify try this

Tab1.Date_Temp = Tab2.Date_Calendrier

Rgds

Anand

Re: LEFT JOIN and RESIDENT

Thu, 15 Sep 2011 16:00:07 GMT

You have to create an intermediate table

Temp:

Noconcatenate

Tab2:

Load

NB_Heure1,

NB_Heure2,

Date_Temp,

Individu

Resident Tab2

Left join

Load

Date_Calendrier as Date_Temp,

Jour,

Mois,

Annee,

Ferie;

Resident Tab1;

Tab3:

Noconcatenate

Load

Individu,

Date_Temp,

if(isnull(Ferie)>0,NB_Heure1+NB_Heure2) as NB_Heure3

Resident Temp;

Drop table Temp;

LEFT JOIN and RESIDENT

Fri, 16 Sep 2011 08:49:01 GMT

Hi Everybody,

thank you for your help!

the exemple of Pauljin is very good...

bye