topic Implementing SubQuery in QlikSense. in App Development

Implementing SubQuery in QlikSense.

poojashribanger — Tue, 21 Aug 2018 06:30:49 GMT

Hi,

How to Implement this in QlikSense.

SEL Dt, SUM(DUP) AS Val,

'2 subs' AS Ar

FROM

(SEL Dt, Id, COUNT(*) AS DUP

FROM table_1

WHERE Dt BETWEEN DAT-90 AND DAT

GROUP BY Dt, Id

HAVING COUNT(*)>1) AS X

GROUP BY 1

ORDER BY 1;

Thanks.

Regards,

Poojashri

Re: Implementing SubQuery in QlikSense.

petter — Tue, 21 Aug 2018 16:55:25 GMT

You can run this SQL against your source and just get the results back. Is it really necessary for you to re-implement this SQL in a load script fashion in any way?

Re: Implementing SubQuery in QlikSense.

petter — Tue, 21 Aug 2018 17:17:10 GMT

If you must do it in a load script it might look something along these lines:

SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';


DAT = Num(MakeDate(2018,6,1));


DATA:
LOAD 
  Dt,
  Sum(DUP) AS Val,
  '2 subs' AS Ar
WHERE
  DUP > 1
GROUP BY 
  Dt;
LOAD
  Dt,
  Id,
  Count(Dt&'|'&Id) AS DUP
INLINE [
Dt,Id, Something
04/01/2018, 1, ABC
05/01/2018, 1, DEF
05/01/2018, 1, GHI
05/01/2018, 2, GHI
05/01/2018, 3, GHI
05/02/2018, 1, GHI
05/23/2018, 1, GHI
05/23/2018, 1, GHI
05/23/2018, 1, GHI
05/23/2018, 1, GHI
]
WHERE
  Dt >= $(DAT)-90 AND Dt <= $(DAT)
GROUP BY
  Dt,
  Id
;

Re: Implementing SubQuery in QlikSense.

poojashribanger — Fri, 24 Aug 2018 07:42:56 GMT

just trying for something new