Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Count(distinct If(ORDTYPE='SL', IsNull(SLS.IVNO))) AS IVCOUNT
Count(distinct If(ORDTYPE='SL', IsNull(SLS.IVNO))) AS IVCOUNT
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
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
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);
Hi PRRAJENDRAN,
guys have suggested right solutions, but you are missing GROUP BY statement as the very last one (after (qvd)...).
M
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
;
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
...(qvd)
GROUP BY yourFieldName1, yourFieldName2, yourFieldName3 ;
Keep order of columns by your grouping preferences.
M
Mark with Correct Answer, if any of the above suggested solutions worked.