topic Re: SQL to Qlikview Case/Then/End in QlikView

SQL to Qlikview Case/Then/End

Wed, 11 May 2016 14:43:49 GMT

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

Re: SQL to Qlikview Case/Then/End

Anonymous — Wed, 11 May 2016 15:32:50 GMT

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.

Re: SQL to Qlikview Case/Then/End

Thu, 12 May 2016 07:58:28 GMT

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?

Re: SQL to Qlikview Case/Then/End

Anonymous — Mon, 16 May 2016 08:09:44 GMT

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

Re: SQL to Qlikview Case/Then/End

Kushal_Chawda — Mon, 16 May 2016 08:18:10 GMT

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

Re: SQL to Qlikview Case/Then/End

PrashantSangle — Mon, 16 May 2016 08:31:44 GMT

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,

Re: SQL to Qlikview Case/Then/End

Mon, 16 May 2016 09:26:00 GMT

HI Max,

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

Re: SQL to Qlikview Case/Then/End

Mon, 16 May 2016 10:32:20 GMT

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?

Re: SQL to Qlikview Case/Then/End

jonathandienst — Mon, 16 May 2016 10:53:30 GMT

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

Re: SQL to Qlikview Case/Then/End

jonathandienst — Mon, 16 May 2016 10:54:26 GMT

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

Re: SQL to Qlikview Case/Then/End

Mon, 16 May 2016 11:02:53 GMT

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;

Re: SQL to Qlikview Case/Then/End

Gabriel — Mon, 16 May 2016 11:27:04 GMT

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

Re: SQL to Qlikview Case/Then/End

kkkumar82 — Mon, 16 May 2016 11:57:59 GMT

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.

Re: SQL to Qlikview Case/Then/End

Anonymous — Mon, 16 May 2016 16:41:38 GMT

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;

Re: SQL to Qlikview Case/Then/End

PrashantSangle — Tue, 17 May 2016 05:01:53 GMT

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,

Re: SQL to Qlikview Case/Then/End

jonathandienst — Tue, 17 May 2016 05:11:31 GMT

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;