4 Replies Latest reply: Apr 12, 2012 4:37 PM by Jose Tos RSS

    Left Join With Where Clause

    Jose Tos

      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.

        • Left Join With Where Clause
          Matthew 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

            • Left Join With Where Clause
              Jose Tos

              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

                • Left Join With Where Clause
                  Jose Tos

                  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))

                    • Left Join With Where Clause
                      Jose Tos

                      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