Implementing SubQuery in QlikSense.

[poojashribanger]

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

[petter]

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?

[petter · Accepted solution]

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
;

[poojashribanger]

just trying for something new