Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author

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
Author

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?

Anonymous
Not applicable
Author

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

Kushal_Chawda

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

PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

HI Max,

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

Not applicable
Author

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?

jonathandienst
Partner - Champion III
Partner - Champion III

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
jonathandienst
Partner - Champion III
Partner - Champion III

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