SQL to Qlikview Case/Then/End

[#1]

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

[#2]

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;

[Gabriel]

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

[kkkumar82]

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.

[Anonymous]

Hi Graham

Try amending the group by as below to include all the columns in your select statement:

Group by [ORDER_NO]&[LINE_NO]&[REL_NO]&[LINE_ITEM_NO],ORDER_NO,DATE_ENTERED,MESSAGE_TEXT;

[PrashantSangle]

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,

[jonathandienst]

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;