Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
bhaveshp90
Contributor III

How to convert this sql into my script ?

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_NumberRisk_Contingency_WBSProbable_PositionRegionSnapshot_DateProbable_Position_wo_LDsProbable_Contractual_PenaltiesFull_impact_Contractual_RisksNet_Full_ImpactProbable_Managed_Impact
P04950168192.74(C) -1942.73999999999ANZ-ASEAN11/30/2017(C) -1942.73999999999(R) 0(R) 0(R) 2900000(R) 113250
P060125395.92(R) 181904.08ANZ-ASEAN11/30/2017(R) 70604.08(R) 111300(R) 445200(R) 591000(R) 176170
P0623310000(C) -36000ANZ-ASEAN11/30/2017(C) -36000(R) 0(R) 0(R) 155000(C) -28600
P0627163815(C) -63815ANZ-ASEAN11/30/2017(C) -63815(R) 0(R) 0(R) 20000(R) 9000

thanks

Bhavesh

1 Solution

Accepted Solutions
jyothish8807
Honored Contributor II

Re: How to convert this sql into my script ?

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

Best Regards,
KC

View solution in original post

11 Replies
qlikviewwizard
Honored Contributor II

Re: How to convert this sql into my script ?

Hi,

Can you attach some dummy data?

bhaveshp90
Contributor III

Re: How to convert this sql into my script ?

I've posted in the question

qlikviewwizard
Honored Contributor II

Re: How to convert this sql into my script ?

Can you show us expected results.

jyothish8807
Honored Contributor II

Re: How to convert this sql into my script ?

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

Best Regards,
KC
bhaveshp90
Contributor III

Re: How to convert this sql into my script ?

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
Honored Contributor II

Re: How to convert this sql into my script ?

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.

Partner
Partner

Re: How to convert this sql into my script ?

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
Honored Contributor II

Re: How to convert this sql into my script ?

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

Best Regards,
KC

View solution in original post

Re: How to convert this sql into my script ?

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