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

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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

[jonathandienst]

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

or

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

[#4]

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

[#5]

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

[mato32188]

Hi PRRAJENDRAN,

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

M

[#7]

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
;

[#8]

hi

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

[mato32188]

...(qvd)

GROUP BY yourFieldName1, yourFieldName2, yourFieldName3 ;

Keep order of columns by your grouping preferences.

M

[#10]

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