topic Re: How to convert this sql into my script ? in QlikView

How to convert this sql into my script ?

bhaveshp90 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have my SQL query as shown below,

CASE

WHEN [Probable_Position_wo_LDs] NOT LIKE '%#%'

THEN CAST(REPLACE(SUBSTRING([Probable_Position_wo_LDs],4,10),',','') AS FLOAT)

END AS [Probable Risk Position w/o Damages (R-C-Cont)],

How can I convert this into my script and load? Any help is greatly appreciated.

Dummy Data:

Project_Number	Risk_Contingency_WBS	Probable_Position	Region	Snapshot_Date	Probable_Position_wo_LDs	Probable_Contractual_Penalties	Full_impact_Contractual_Risks	Net_Full_Impact	Probable_Managed_Impact
P04950	168192.74	(C) -1942.73999999999	ANZ-ASEAN	11/30/2017	(C) -1942.73999999999	(R) 0	(R) 0	(R) 2900000	(R) 113250
P06012	5395.92	(R) 181904.08	ANZ-ASEAN	11/30/2017	(R) 70604.08	(R) 111300	(R) 445200	(R) 591000	(R) 176170
P06233	10000	(C) -36000	ANZ-ASEAN	11/30/2017	(C) -36000	(R) 0	(R) 0	(R) 155000	(C) -28600
P06271	63815	(C) -63815	ANZ-ASEAN	11/30/2017	(C) -63815	(R) 0	(R) 0	(R) 20000	(R) 9000

thanks

Bhavesh

Re: How to convert this sql into my script ?

qlikviewwizard — Tue, 11 Sep 2018 03:16:57 GMT

Hi,

Can you attach some dummy data?

Re: How to convert this sql into my script ?

bhaveshp90 — Tue, 11 Sep 2018 03:22:20 GMT

I've posted in the question

Re: How to convert this sql into my script ?

qlikviewwizard — Tue, 11 Sep 2018 05:22:00 GMT

Can you show us expected results.

Re: How to convert this sql into my script ?

jyothish8807 — Tue, 11 Sep 2018 06:10:41 GMT

Hi Bhavesh,

Try like this:

if(not wildmatch([Probable_Position_wo_LDs],'%#%'),

num(left(right([Probable_Position_wo_LDs],len([Probable_Position_wo_LDs])-2),10))) as [Probable_Position_wo_LDs],

END AS [Probable Risk Position w/o Damages (R-C-Cont)]

from <your table>

Br,

Re: How to convert this sql into my script ?

bhaveshp90 — Tue, 11 Sep 2018 12:22:00 GMT

This is the Error message I am getting through "Field not found: Field End not found"

Script:

Risk_Register1:

NoConcatenate load

if(not wildmatch([Probable_Position_wo_LDs],'%#%'),num(left(right([Probable_Position_wo_LDs],len([Probable_Position_wo_LDs])-2),10))) as [Probable_Position_wo_LDs], END AS [Probable Risk Position w/o Damages (R-C-Cont)]

Resident Risk_Register;

drop table Risk_Register;

Re: How to convert this sql into my script ?

trdandamudi — Tue, 11 Sep 2018 12:45:42 GMT

how about the below:

if(not wildmatch([Probable_Position_wo_LDs],'%#%'),

num(left(right([Probable_Position_wo_LDs],len([Probable_Position_wo_LDs])-2),10))) as [Probable_Position_wo_LDs],

[END] AS [Probable Risk Position w/o Damages (R-C-Cont)]

Note: I used Jyothish code and wrapped the "END" in square brackets.

Re: How to convert this sql into my script ?

stigchel — Tue, 11 Sep 2018 13:01:08 GMT

I think the "END" is the closing statement of the sql CASE command, so

if(not wildmatch([Probable_Position_wo_LDs],'%#%'),num(left(right([Probable_Position_wo_LDs],len([Probable_Position_wo_LDs])-2),10))) as [Probable Risk Position w/o Damages (R-C-Cont)]

Re: How to convert this sql into my script ?

jyothish8807 — Tue, 11 Sep 2018 13:10:01 GMT

My bad.

"END" is the keyword to close the case statement.

NoConcatenate load

if(not wildmatch([Probable_Position_wo_LDs],'%#%'),

num(left(right([Probable_Position_wo_LDs],len([Probable_Position_wo_LDs])-2),10))) as [Probable_Position_wo_LDs]

Resident Risk_Register;

drop table Risk_Register;

Br,

Re: How to convert this sql into my script ?

Miguel_Angel_Baeyens — Tue, 11 Sep 2018 14:12:40 GMT

Be aware that '%#%' will be taken as a literal string, meaning "%#%". QlikView accepts only two wildcards: ? for one character and * for none, one or more character. The hash sign or pound sign "#" does not mean "number".

So your expression above will always go to the else part of the If(), because it will not be true unless the string %#% is found in the field [Probable_Position_wo_LDs]

For that you will need to use instead something like

Len(KeepChar([Probable_Position_wo_LDs], '0123456790,.-'))

meaning there is at least one of those characters in the string, considering the negative "-" sign, decimal and thousand separators as "," and ".".

Re: How to convert this sql into my script ?

bhaveshp90 — Tue, 11 Sep 2018 14:19:36 GMT

The script loaded correctly when I removed the end statement

Re: How to convert this sql into my script ?

bhaveshp90 — Tue, 11 Sep 2018 14:20:47 GMT

That was very helpful, thank you