Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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?
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
Why don't you directly run this query from qlikview and store the data into the QVD?
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,
HI Max,
Thank You, however this doesn't appear to work, I think there is something slightly wrong with the syntax?
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?
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).
>>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?