15 Replies Latest reply: May 17, 2016 1:12 AM by Jonathan Dienst RSS

    SQL to Qlikview Case/Then/End

    Graham Cooper

      Hi all,

       

      I think this should be quite simple but I am  a bit of a newb and think I would learn more being pointed in the right direction.

       

      The below is part of an SQL script that I have inherited and am attempting to translate it to Qlikview, I have had a few goes but need a little more guidance.

       

      left outer join (select order_no,line_no,rel_no,line_item_no,
      min(case when message_text = 'Reserved' then trunc(date_entered) end) reserved,
      min(case when message_text = 'Picked' then trunc(date_entered) end) picked,
      min(case when message_text = 'Delivered' then trunc(date_entered) end) delivered
      from ifsapp.customer_order_line_hist h
      group by order_no,line_no,rel_no,line_item_no) dsplh2
      on dspl2.order_no = dsplh2.order_no
      and dspl2.line_no = dsplh2.line_no
      and dspl2.rel_no = dsplh2.rel_no
      and dspl2.line_item_no = dsplh2.line_item_no

      Its mostly the parts underlined that I need help translating, I'm alright with the rest.

       

      Thank You

        • Re: SQL to Qlikview Case/Then/End
          Brian Copple

          Hi Graham

          If you tick the preceding load checkbox when you select the database to connect to and choose the fields you require you will see something similar to this

          LOAD field A,

          Field B etc

          SQL Select field A,

          Case when Field B ='1' then do something else

          from database

          You will then be able to use your SQL case statement in the SQL select portion of your script as illustrated in the bold font above. If you have any problems just ask. Good luck.

          • Re: SQL to Qlikview Case/Then/End
            Brian Copple

            Hi Graham

            Yes you could use nested if statements for example:

            if (min(case when message_text = 'Reserved',then whatever output you require,

            if(min(case when message_text = 'Picked' then output))

             

            Remember as you are using the min function you will need to use group by so it may be better to do this as either a preceding or resident load. Could you share an example of your script for clarity.

            Kindest Regards

            B

              • Re: SQL to Qlikview Case/Then/End
                Graham Cooper

                Hi Brian,

                 

                Translating your above into what I think is correct my script looks like the below:

                 

                Customer_Order_Line_Hist:
                LOAD
                [ORDER_NO]&[LINE_NO]&[REL_NO]&[LINE_ITEM_NO] as COLINEKEY,
                Date(Floor(DATE_ENTERED),'DD/MM/YYY') AS DATE_ENTERED,
                MESSAGE_TEXT AS CUSTOMER_ORDER_LINE_HIST_MESSAGE_TEXT,

                if (min(case when message_text = 'Reserved' then floor(DATE_ENTERED)) as RESERVED,
                if(min(case when message_text = 'Picked' then floor(DATE_ENTERED)) as PICKED,
                if(min(case when message_text = 'Delivered' then floor(DATE_ENTERED)) as DELIVERED)))

                FROM
                $(vQVDPath)CUSTOMER_ORDER_LINE_HIST.qvd
                (QVD)
                Group by ORDER_NO

                 

                However he syntax is throwing a wobbled passed the first 'when'

                 

                Thoughts?

              • Re: SQL to Qlikview Case/Then/End
                kushal chawda

                Why don't you directly run this query from qlikview and store the data into the QVD?

                • Re: SQL to Qlikview Case/Then/End
                  Prashant Sangle

                  Hi,

                   

                  try with simple if else statement

                   

                  try like

                   

                  min(if(message_text='Reserved',floor(date_entered)) as reserved,

                  min(if(message_text='Picked',floor(date_entered)) as Picked,

                  min(if(message_text='Delivered',floor(date_entered)) as Delivered


                  Regards,

                    • Re: SQL to Qlikview Case/Then/End
                      Graham Cooper

                      HI Max,

                       

                      Thank You, however this doesn't appear to work, I think there is something slightly wrong with the syntax?

                        • Re: SQL to Qlikview Case/Then/End
                          Jonathan Dienst

                          >>Thank You, however this doesn't appear to work, I think there is something slightly wrong with the syntax?

                           

                          Did you include Group By clause?

                            • Re: SQL to Qlikview Case/Then/End
                              Graham Cooper

                              Hello,

                               

                              Yes I did, taking the above, I'm still not getting any further than the first 'if',

                               

                              Customer_Order_Line_Hist:
                              LOAD
                              [ORDER_NO]&[LINE_NO]&[REL_NO]&[LINE_ITEM_NO] as COLINEKEY,
                              ORDER_NO,
                              DATE_ENTERED,
                              MESSAGE_TEXT,

                              Min(If(MESSAGE_TEXT = 'Reserved', floor(DATE_ENTERED)) as reserved,

                              Min
                              (If(MESSAGE_TEXT = 'Picked', floor(DATE_ENTERED)) as picked,

                              Min
                              (If(MESSAGE_TEXT = 'Delivered', floor(DATE_ENTERED)) as delivered,

                              FROM
                              $(vQVDPath)CUSTOMER_ORDER_LINE_HIST.qvd
                              (QVD)
                              Group by ORDER_NO;

                                • Re: SQL to Qlikview Case/Then/End
                                  Prashant Sangle

                                  Hi,

                                   

                                  there is one closing bracket missing

                                  add it at end of date_entered and try

                                   

                                  min(if(message_text='Reserved',floor(date_entered))) as reserved,

                                  min(if(message_text='Picked',floor(date_entered))) as Picked,

                                  min(if(message_text='Delivered',floor(date_entered))) as Delivered

                                   

                                  Regards,

                                  • Re: SQL to Qlikview Case/Then/End
                                    Jonathan Dienst

                                    Every field not inside an aggregation function needs to be listed in the group by clause:

                                     

                                    Customer_Order_Line_Hist:

                                    LOAD

                                         [ORDER_NO]&[LINE_NO]&[REL_NO]&[LINE_ITEM_NO] as COLINEKEY,

                                         ORDER_NO,

                                         DATE_ENTERED,

                                         MESSAGE_TEXT,

                                         Min(If(MESSAGE_TEXT = 'Reserved', floor(DATE_ENTERED))) as reserved,

                                         Min(If(MESSAGE_TEXT = 'Picked', floor(DATE_ENTERED))) as picked,

                                         Min(If(MESSAGE_TEXT = 'Delivered', floor(DATE_ENTERED))) as delivered,

                                    FROM $(vQVDPath)CUSTOMER_ORDER_LINE_HIST.qvd (QVD)

                                    Group by [ORDER_NO],

                                      [LINE_NO],

                                      [REL_NO],

                                      [LINE_ITEM_NO],

                                      MESSAGE_TEXT;

                                • Re: SQL to Qlikview Case/Then/End
                                  Gabriel Oluwaseye

                                  Hi,

                                   

                                  Try this

                                   

                                  Min(If(Wildmatch(message_text, '*Reserved*')>0, Floor(date_entered))          AS [Reserved Date],

                                   

                                  Min(If(Wildmatch(message_text,'*Picked*')>0, Floor(date_entered))           AS [Picked Date],

                                   

                                  Min(If(Wildmatch(message_text,'*Delivered*')>0, Floor(date_entered))          AS [Delivered Date]

                                   

                                   

                                  You will have to use Group By

                              • Re: SQL to Qlikview Case/Then/End
                                AC BC

                                Hi Graham,

                                It seems your data source is Oracle EBS, if so, try to know what they want using the SQL, you can take any of the SQL developers help , because you can handle all those things in the front end other wise it is a night mare doing all those left join stuff with Oracle functions.