topic Re: urgent pls............sql case to qlikview in QlikView

urgent pls............sql case to qlikview

Fri, 20 Nov 2020 18:02:30 GMT

Hi experts,

I need to use this sql expression in qlikview load script.

Expression =COUNT(DISTINCT CASE WHEN ORDTYPE='SL' THEN ISNULL(SLS.IVNO,'') END) AS IVCOUNT

How can i do this.

Please help in this regard

Re: urgent pls............sql case to qlikview

Gysbert_Wassenaar — Tue, 05 May 2015 12:53:03 GMT

Count(distinct If(ORDTYPE='SL', IsNull(SLS.IVNO))) AS IVCOUNT

Re: urgent pls............sql case to qlikview

jonathandienst — Tue, 05 May 2015 13:02:10 GMT

ISNULL in SQl works differently to ISNULL in QV. I think this might be more accurate:

=COUNT(DISTINCT If(ORDTYPE='SL', If(Len(SLS.IVNO) > 0, SLS.IVNO, ''))) AS IVCOUNT

=COUNT(DISTINCT If(ORDTYPE='SL', If(IsNull(SLS.IVNO), SLS.IVNO, ''))) AS IVCOUNT

Re: urgent pls............sql case to qlikview

Tue, 05 May 2015 13:53:44 GMT

hi,

thanks for the reply.

i have tried both, but i m getting error as invalid expression.

this is my script.

Sales:

LOAD BRCODE,

COUNT(DISTINCT If(ORDTYPE='SL', If(Len(IVNO) > 0,IVNO, ''))) AS IVCOUNT ,

IVDT,

sum(SPAMT) as NETSL,

CPAMT

FROM

[\QVData\SALES.qvd]

(qvd);

any other suggestions plssssss

Re: urgent pls............sql case to qlikview

Tue, 05 May 2015 14:06:42 GMT

Try this..

Sales:

LOAD BRCODE,

COUNT(DISTINCT If(ORDTYPE='SL', If(Len(IVNO) = 0,'NULL',IVNO))) AS IVCOUNT ,

IVDT,

sum(SPAMT) as NETSL,

CPAMT

FROM

[\QVData\SALES.qvd]

(qvd);

Re: urgent pls............sql case to qlikview

mato32188 — Tue, 05 May 2015 14:08:21 GMT

Hi PRRAJENDRAN,

guys have suggested right solutions, but you are missing GROUP BY statement as the very last one (after (qvd)...).

Re: urgent pls............sql case to qlikview

Tue, 05 May 2015 14:15:16 GMT

With Group By

Sales:

BRCODE,
COUNT(DISTINCT If(ORDTYPE='SL', If(Len(IVNO) = 0,'NULL',IVNO))) AS IVCOUNT ,
     IVDT,
    sum(SPAMT) as NETSL,

     CPAMT
FROM
[\QVData\SALES.qvd](qvd)
GROUP BY
BRCODE,
IVDT,
CPAMT
;

Re: urgent pls............sql case to qlikview

Tue, 05 May 2015 14:16:17 GMT

how should i give that group by.

can u please help me with the script.???

actually i have used this

group by BRCODE

but still error

Re: urgent pls............sql case to qlikview

mato32188 — Tue, 05 May 2015 14:24:39 GMT

...(qvd)

GROUP BY yourFieldName1, yourFieldName2, yourFieldName3 ;

Keep order of columns by your grouping preferences.

Re: urgent pls............sql case to qlikview

Tue, 05 May 2015 15:06:58 GMT

Mark with Correct Answer, if any of the above suggested solutions worked.