How to convert this sql into my script ?

[bhaveshp90]

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

[qlikviewwizard]

Hi,

Can you attach some dummy data?

[bhaveshp90]

I've posted in the question

[qlikviewwizard]

Can you show us expected results.

[jyothish8807]

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,

KC

[bhaveshp90]

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;

[trdandamudi]

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.

[stigchel]

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

[jyothish8807 · Accepted solution]

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,

KC

[Miguel_Angel_Baeyens]

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