Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

SQL to Qlikview Case/Then/End

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

15 Replies
briancopple
Contributor II

Re: SQL to Qlikview Case/Then/End

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.

Not applicable

Re: SQL to Qlikview Case/Then/End

Hi Brian,

Thank You for your reply, however I am trying to avoid writing anything in SQL as I just don't understand it enough.

Is there no way to write the SQL in a Qlik syntax?

briancopple
Contributor II

Re: SQL to Qlikview Case/Then/End

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

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

Re: SQL to Qlikview Case/Then/End

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,

Regards,
Prashant Sangle
Not applicable

Re: SQL to Qlikview Case/Then/End

HI Max,

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

Not applicable

Re: SQL to Qlikview Case/Then/End

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?

MVP
MVP

Re: SQL to Qlikview Case/Then/End

These expressions should serve:

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,

Remember that the load will need a Group By clause for all the non-aggregated fields (ie not in a Min/Max/Sum/Count/etc function).

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: SQL to Qlikview Case/Then/End

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser