Left Join With Where Clause

[chematos]

Hi everyone,

I´ve tried to follow the example of the link to solve the problem I´m going to expose: http://community.qlik.com/message/141239#141239

At first, I have a table with NroMaterial as identifier, %Fecha as a date por each row and SSTT as a value that is null in many rows so I want to replace that null values to the average for each NroMaterial that it´s in another table and I want the closest average before the date of the first table.

So, I have a table called Final with:

NroMaterial,

%Fecha,

SSTT

and other table with the averages called MediasSolidos:

NroMaterial,

[%Fecha cre], (this must be the closest date to %Fecha but always less than %Fecha)

Media (this is the average for the NroMaterial)

I´ve tried with this code but is not working. Any help will be apreciated.

Se aceptan respuestas en Español por supuesto.

NoConcatenate

Vacios:

Load NroMaterial,

           %Fecha as Dia

Resident Final

where isnull(SSTT);

Left Join(Vacios)

Load

NroMaterial,

[%Fecha cre] as Date,

Media

Resident MediasSolidos;

Left Join(Vacios)

LOAD *

Resident Vacios

where max(Date) < Dia;

Regards ,

Chema.

[matt_crowther]

Chema,

By the looks of the last left join statement you're going to run into difficulties as you're loading all the columns from Vacios, restricting them by date and then left joining them back to Vacios - this won't work as all the field names are the same.

I'm not entirely sure what you're trying to do so forgive me if any of these suggestions aren't applicable but hopefully they'll point you in the right dicrection.

1. To limit by date simply create a 'Temp_Vacios' table and instead of the final left join simply perform:

Vacios:

Load *

Resident Temp_Vacios

Where max(Date)<Dia;

Drop Table Temp_Vacios;

2. You may also be getting problems due the max() function in the Where clause, to fix; set the max(Date) in a variable and then call in from the Where statement.

Date_Temp:

Load Distinct

     Date

Resident Temp_Vacios Order By Date;

Let v_Max_Date = peek('Date')

Drop Table Date_Temp;

Vacios:

Load *

Resident Temp_Vacios

Where $(v_Max_Date)<Dia;

None of that has been checked.

3. You can also get your average during a left join similar to:

Sales_Data:

Load Sales_Person,

     Date,

     Sales

Resident MySpreadsheet.xls;

Left join (Sales_Data)

Load Sales_Person,

     sum(Sales) as Total_Sales

Resident Sales_Data Group By Sales_Person;

Again not checked. You can see all the field we're left joining is named differently (Total_Sales) to the one we have in the original Sales_Data field.

Hope that's at least some use.

All the best,

Matt - Visual Analytics Ltd

Qlikview Design Blog: http://QVDesign.wordpress.com

Twitter: @QlikviewBI

[chematos]

Matt,

My first thought was to do it like your point number 1. But the problem is that Date and Dia are fields from different tables so I can´t to compare them in the where clause beacuse only Date belongs to the resident table.

That´s why I was looking for a distinct solution.

I realized that I was getting problems using max()  function so thank you for your solution, I´m going to try that.

I´m trying to do all this with set expression but I think is not the best idea but if someone has an easier solution with set expression...it will be welcome!!

Thank you Matt, I keep trying.

Chema

[chematos]

Hi again,

With the next expression I get the last average of the NroMaterial but I need the last average with Fecha_Media less than %Fecha.

if(SSTT>0,SSTT,Avg({$<Fecha_Media = {"<=$(=max(Fecha_Media))"} >}Media))

I´ve tried this but I need help with the syntax of the second condition:

,Fecha_Media={"Fecha_Media < {%Fecha} "}

if(SSTT>0,SSTT,Avg({$<Fecha_Media = {"<=$(=max(Fecha_Media))"},

Fecha_Media={"Fecha_Media < {%Fecha} "} >}Media))

[chematos · Accepted solution]

Al fin pude resolverlo!!!

He tenido que usar unas cuantas tablas intermedias pero ha resultado y estoy feliz.

Es una pena que en la carga no se tengan todas las posibilidades que da el SQL, hubiera resultado más fácil.

Si alguien tiene curiosidad sobre cómo lo resolví que no dude en contactarme. No la voy a copiar aquí porque es un poco larga y puede ser enrevesada.

Saludos,

Chema