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

    Implementing a Left Join and a Transformation



      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
          Mayil Vahanan Ramasamy



          Did you expect something like this?



          Load  * Inline






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







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


          so in this case, for


          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:




            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:




              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.




              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.




              Let me know if this is helpful




              Mike T