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
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;
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
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.
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;
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,
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;