8 Replies Latest reply: May 11, 2016 10:33 AM by Graham Cooper RSS

    sql Translation

    Graham Cooper

      Hello,

       

      I have inherited some script, below is a short extract of it.

       

      How would I convert that into a standard Qlikview script?

       

      I know the question is a bit vague but I'm happy to add further details as answers come in.

       

      left outer join ifsapp.ard_po_line_part_view pol2
      on copo2.po_order_no = pol2.order_no
      and copo2.po_line_no = pol2.line_no
      and copo2.po_rel_no = pol2.release_no
      and copo2.purchase_type_db = 'O'
      and pol2.state != 'Cancelled'

       

      Thank you

        • Re: sql Translation
          Alex Pan

          I think in the scenario, it is the best you connect directly to the database(if possible) and run the SQL and get the final dataset.

          Can you connect to the database?

          • Re: sql Translation
            John Witherspoon

            A simple answer is:

             

            SQL
            ...whatever the rest of the SQL statement is...
            left outer join ifsapp.ard_po_line_part_view pol2
            on copo2.po_order_no = pol2.order_no
            and copo2.po_line_no = pol2.line_no
            and copo2.po_rel_no = pol2.release_no
            and copo2.purchase_type_db = 'O'
            and pol2.state != 'Cancelled'
            ;

             

            But if you're looking more for "what's the generic equivalent of something like this in QlikView", I believe something like this for tables that are already loaded.

             

            LEFT JOIN (copo2)

            LOAD

            order_no as po_order_no

            ,line_no as po_line_no

            ,release_no as po_rel_no

            ,'O' as purchase_type_db

            RESIDENT pol2

            WHERE state <> 'Cancelled'

            ;

             

              • Re: sql Translation
                Alex Pan

                I think this will not make the join since names are different. Hence they will not be correctly joined even names are changed.

                 

                copo2.po_order_no = pol2.order_no

                and copo2.po_line_no = pol2.line_no

                and copo2.po_rel_no = pol2.release_no

                  • Re: sql Translation
                    John Witherspoon

                    If you're talking about the SQL, it looks like it should work, but I can't prove it since I don't have the tables.

                     

                    If you're talking about my script, it seems to work fine. I've added a field "something" to pol2 and loaded it, since otherwise the join does nothing. It's the structure for joining values, but I didn't add values to it. Maybe I misunderstood the SQL snippet, but joining on different field names works just fine since I'm renaming them during the join.

                     

                    copo2:
                    LOAD * INLINE [
                    po_order_no, po_line_no, po_rel_no, purchase_type_db
                    A, 1, 1, X
                    A, 1, 1, O
                    ]
                    ;
                    pol2:
                    LOAD * INLINE [
                    order_no, line_no, release_no, state, something
                    A, 1, 1, Active, Good
                    A, 1, 1, Cancelled, Bad
                    ]
                    ;

                    LEFT JOIN (copo2)
                    LOAD
                    order_no as po_order_no
                    ,
                    line_no as po_line_no
                    ,
                    release_no as po_rel_no
                    ,'O'
                    as purchase_type_db
                    ,
                    something
                    RESIDENT pol2
                    WHERE state <> 'Cancelled'
                    ;

                    DROP TABLE pol2;

                     

                    Capture.PNG

                      • Re: sql Translation
                        Alex Pan

                        Will this create a synthetic key and it should be something to avoid? and even if you want to do that in SQL, i think that it should look like this

                        copo2:

                        po_order_no& po_line_no&release_no as Key

                        ,.....

                        from copo2;

                        LEFT JOIN (copo2)
                        LOAD
                        order_no &line_no as po_line_no&release_no as Key
                        ,something
                        RESIDENT pol2
                        WHERE state <> 'Cancelled' and purchase_type_db='O'
                        ;

                          • Re: sql Translation
                            John Witherspoon

                            No, it won't create a synthetic key, which you can see by opening up the table view in the QVW I posted. There's only one table in the final data model, no composite or synthetic key.

                             

                            A synthetic key isn't always something to avoid:

                             

                            Should We Stop Worrying and Love the Synthetic Key?

                             

                            Though if you read that, please read the WHOLE thread, not just my initial post. I WAS shown a practical counterexample to my initial contention, I made a mistake in how I was tracking load times, synthetic keys in the wild usually ARE an indication that someone has made a mistake, even if you intend a multi-field key to link two tables you might want to build it deliberately rather than by default, and, as is true in our case here, the data model should perhaps be redone so that you need NEITHER a synthetic nor a composite key.

                             

                            As you've identified, the composite "key" to the table seems to be the combination of order number, line number, and release number. But both of our tables have this same key, and no other key data. Therefore, they can and in QlikView I'd argue should be considered the same table. For whatever reason, in our database, we built multiple tables with the same key. There can be good reasons for this - we have plenty of cases like that around here. When loading into QlikView, we just join them back into one table again, but we do it in the SQL, not in QlikView script (well, except in the sense that the SQL is written in the QlikView Script).

                             

                            As far as building a composite key when doing a left join, that would work but is absolutely unnecessary. QlikView will join on all common fields by name, just as SQL can join on all common fields as identified in your ON or WHERE. And there's a possibility of error as well, such as if you have order number 11, line number 1, release number 1, and order number 1, line number 11, release number 1, or any other such combination. They would all get the same key value in your script. Typically I'll separate the key fields by a character that never appears in the key fields, often a ':'. But again, unnecessary here, and an opportunity to make a mistake.

                             

                            There's a problem with how you're checking for purchase type db = 'O'. This field is only in copo2, so you can't put it in a where condition when loading from pol2. One way around that is to do what I show, load 'O' as purchase_type_db, which will cause you to only join to records in copo2 that have 'O', which accomplishes what we're after.

                    • Re: sql Translation
                      Graham Cooper

                      Thank You all for the above.

                      Apologies for delay but this is workplace orientated issue and I have not seen the replies over the weekend.

                      John's solution feels like it is in right direction and I shall have a tinker this week and let you know.