Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
bhaveshp90
Contributor III

How to convert this SQL statement in my script?

Hello,

I have this SQL case statement for summing up the values after converting as shown below;

CASE WHEN NCC.ncc_type='-' THEN

SUM

(

     CASE  WHEN  NCC.[NCC_ve_NCC] IS NULL THEN ''

           WHEN NCC.[NCC_ve_NCC] LIKE '%.''%' THEN CAST(REPLACE(LTRIM(RTRIM(NCC.[NCC_ve_NCC])),'.''','.') AS FLOAT)

            ELSE CAST(REPLACE(REPLACE(LTRIM(RTRIM(NCC.[NCC_ve_NCC])),',',''),',','') AS FLOAT)

     END

)

ELSE 0

END AS 'Overrun_Cost',

How can I include in my script? Any help is appreciated.

thanks

Bhavesh

1 Solution

Accepted Solutions
Highlighted
jyothish8807
Honored Contributor II

Re: How to convert this SQL statement in my script?

Try this once:

=if(NCC.ncc_type = '-',

          if(isnull( NCC.[NCC_ve_NCC]),'',

                    if(wildmatch(NCC.[NCC_ve_NCC],'%.''%'),   num(replace(trim(NCC.[NCC_ve_NCC]),'.''','.')),

REPLACE(REPLACE(TRIM(NCC.[NCC_ve_NCC]),',',''),',','')

)),0)

as Overrun_Cost

resident <>;


Need to check the brackets.


Br,

KC

Best Regards,
KC

View solution in original post

2 Replies
Highlighted
jyothish8807
Honored Contributor II

Re: How to convert this SQL statement in my script?

Try this once:

=if(NCC.ncc_type = '-',

          if(isnull( NCC.[NCC_ve_NCC]),'',

                    if(wildmatch(NCC.[NCC_ve_NCC],'%.''%'),   num(replace(trim(NCC.[NCC_ve_NCC]),'.''','.')),

REPLACE(REPLACE(TRIM(NCC.[NCC_ve_NCC]),',',''),',','')

)),0)

as Overrun_Cost

resident <>;


Need to check the brackets.


Br,

KC

Best Regards,
KC

View solution in original post

Highlighted
bhaveshp90
Contributor III

Re: How to convert this SQL statement in my script?

thank you it worked