3 Replies Latest reply: Nov 6, 2012 3:59 PM by Michael Tarallo RSS

    Implementing a Left Join and a Transformation

    Ernesto Moscoso

      Hello

       

      I have two inputs that are as follow:

       

      MyMainTable (#KeyField, TransformingField, ...)

      MyHelperView (#KeyField, MyFlag) -- MyFlag's value is always 1

       

      So, I want to joined them in order to get a compound input that includes MyFlag from MyHelperView. The joined has to be in such a way that:

      - When a match is found, TransformingField has to be changed to 0.

      - When a match is not found, the record is left alone (not transformation is required).

       

      Later on I will combine both outputs into a single stream using a Funnel.

       

      Can you please my with a sample about how to do this?

       

      When using the Join operator, what is the equivalent of a Left Join and the SQL expression IsNull(MyFlag, 0) ?

       

       

      Thank you very much!

        • Re: Implementing a Left Join and a Transformation
          mayilvahanan ramasamy

          HI

           

          Did you expect something like this?

           

          keyJoin:

          Load  * Inline

          [

          #keyField,TransformingField

          xxx,1

          ];

          join(keyJoin)

          Load #keyField,if(Exists(#keyField,#keyField),1,0) as MyFlag Inline

          [

          #keyField,MyFlag

          xxx,1

          zzz,1

          ];

           

          if keyfield is exists in first table, then 1 is set to my flag else zero set to it..

           

          so in this case, for

          keyfield,MyFlag

          xxx ,1

          zzz ,0

           

          will assign.

          • Re: Implementing a Left Join and a Transformation
            Michael Tarallo

            Hello emoscosocam -

             

            Have you had a chance to look at this in the documentation yet:

             

            http://documentation.qlik.com/expressor/3.8/expressor_operator_summary/Join.htm

             

            Let me see if I can whip up a quick sample for you

             

            Mike T

            • Re: Implementing a Left Join and a Transformation
              Michael Tarallo

              Hello emoscosocam -

               

              See attached QlikView Expressor project in this post. I created it with QlikView Expressor 3.8.2 - please make sure you use that version before importing it.

               

              Please note the name of the step in the Dataflow I created is named use_case - it is currently enabled. I did create other join example steps (currently disabled) so you can see how some of the joins work and the resulting data.

               

              Note that all of the sample data is included and the file connection points to the following location where you can review the files:

               

              <drive letter>:\<directory of your workspace>\Metadata\join_278146.0\external

               

              I believe the way this is achieved with QlikView Expressor is as follows see screen-shot:

               

              dataflow1.PNG

               

              I joined the two sources (Department and Employees) on a common key (dept_id and dep_id) using the Join operator set to outer - this will include all records of those that are matching and those that do not match from both sources.I also blocked the key attributes from propagating over.

               

              trans1.PNG

               

              I then use a Transform operator with the following decision logic in an Expression Rule: decision(input.key1==input.key_2,0,input.transform_field)

               

              The value here simply rempas over the existing transform_field aatribute - note the blue diamond shape.

               

              This ensures that if the two keys are equal then make the transform_field value 0 else use the original value (input.transform_field) and pass it through as is.

               

              As a result, all the matching key records transform_fields are now 0 and the original missing records are passed un-touched but included in the final output containing empty or null values.

               

              note1.PNG

               

              Let me know if this is helpful

               

              Regards,

               

              Mike T