Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
david_balla
New Contributor II

LOAD & SQL with function count disinct

Hello,

Please would you mind helping me with the script below, everything is working until I put this command "count (distinct hashcode) as početpacientov"

I dont know how wirte script with count nor with count (distinct)

Thank you for your help


David

MAINTABLE:

LOAD

DatumPrepustenia,

    Vek,

    left([KodDiagnozyPriPrepusteni],3) as KodDiagnozyPriPrepusteni,

    left([KodPzs],6) as KodPzs,

ApplyMap('Mapping_ZP', Poistovna) as ZdravotnaPoistovna,

    Mid(KodPzs, 7, 3) as Špec,

    TypVykonu,

    PocetOperacnychVykonov,

    [UhradaPoistovne],

    count (distinct hashcode) as početpacientov;

SQL SELECT

DatumPrepustenia,

    Vek,

    KodDiagnozyPriPrepusteni,

    KodPzs,

Poistovna,

    TypVykonu,

    PocetOperacnychVykonov,

    UhradaPoistovne,

    hashcode

FROM "datazp_imp".dbo.UstavnaStarostlivost

where year(DatumPrepustenia) in ('2016','2017','2018')

group by DatumPrepustenia,

Vek,

    KodDiagnozyPriPrepusteni,

    KodPzs,

Poistovna,

    TypVykonu,

    PocetOperacnychVykonov,

    hashcode,

    UhradaPoistovne,

    hashcode;

8 Replies
Highlighted
Partner
Partner

Re: LOAD & SQL with function count disinct

Looks like you are doing a group by on the SQL. you should

- do count on the sql

- or load sql without group by. And either do preceding load or a resident load with group by and count distinct

Highlighted
david_balla
New Contributor II

Re: LOAD & SQL with function count disinct

Dilip, I am not sure if I understood you correctly, could you wirte it to the script these two options? I am afraid that I already tried booth option, unfortunately it didnt work.

thanks

david

Highlighted

Re: LOAD & SQL with function count disinct

Looks like below script having double time. May be try this?

MAINTABLE:

LOAD distinct

DatumPrepustenia,

    Vek,

    left([KodDiagnozyPriPrepusteni],3) as KodDiagnozyPriPrepusteni,

    left([KodPzs],6) as KodPzs,

ApplyMap('Mapping_ZP', Poistovna) as ZdravotnaPoistovna,

    Mid(KodPzs, 7, 3) as Špec,

    TypVykonu,

    PocetOperacnychVykonov,

    [UhradaPoistovne],

    count (hashcode) as početpacientov

// Group By Must includes all Non Aggregate fields like

Group By

DatumPrepustenia,

    Vek,

    left([KodDiagnozyPriPrepusteni],3),

    left([KodPzs],6),

ApplyMap('Mapping_ZP', Poistovna),

    Mid(KodPzs, 7, 3),

    TypVykonu,

    PocetOperacnychVykonov,

    [UhradaPoistovne];

SQL SELECT

DatumPrepustenia,

    Vek,

    KodDiagnozyPriPrepusteni,

    KodPzs,

Poistovna,

    TypVykonu,

    PocetOperacnychVykonov,

    UhradaPoistovne,

    hashcode

FROM "datazp_imp".dbo.UstavnaStarostlivost

where year(DatumPrepustenia) in ('2016','2017','2018')

group by DatumPrepustenia;

Vek,

    KodDiagnozyPriPrepusteni,

    KodPzs,

Poistovna,

    TypVykonu,

    PocetOperacnychVykonov,

    hashcode,

    UhradaPoistovne,

    hashcode;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
david_balla
New Contributor II

Re: LOAD & SQL with function count disinct

thank you so much, it doesnt work, group by should be in sql select, not in load fucntion as far as I know

thanks
David

Highlighted
Partner
Partner

Re: LOAD & SQL with function count disinct

- Do count on sql

LOAD CustCounts,

    EmployeeID;

SQL SELECT count(CustomerID) as CustCounts,

    EmployeeID,   

FROM Orders

group By  

EmployeeID;

- Load sql without groupby and do it on preceding load

LOAD count( CustomerID),

    EmployeeID

group By

EmployeeID

    ;

SQL SELECT CustomerID,

    EmployeeID,

    Freight,

    OrderDate  

FROM Orders;

Highlighted
afroz_shaik
New Contributor III

Re: LOAD & SQL with function count disinct

Try this:

Mapping_ZP:

Load Poistovna,

         XXXX;

MAINTABLE:

LOAD

          DatumPrepustenia,

          Vek,

         left([KodDiagnozyPriPrepusteni],3) as KodDiagnozyPriPrepusteni,

         left([KodPzs],6) as KodPzs,

ApplyMap('Mapping_ZP', Poistovna,'elsecondition') as ZdravotnaPoistovna,

         Mid(KodPzs, 7, 3) as Špec,

         TypVykonu,

         PocetOperacnychVykonov,

         [UhradaPoistovne],

         početpacientov;

SQL

SELECT DatumPrepustenia,

               Vek,

               KodDiagnozyPriPrepusteni,

               KodPzs,

               Poistovna,

              TypVykonu,

              PocetOperacnychVykonov,

              UhradaPoistovne,

              count (distinct hashcode) as početpacientov

FROM "datazp_imp".dbo.UstavnaStarostlivost

where year(DatumPrepustenia) in ('2016','2017','2018')

group by DatumPrepustenia,

               Vek,

               KodDiagnozyPriPrepusteni,

               KodPzs,

               Poistovna,

              TypVykonu,

              PocetOperacnychVykonov,

              hashcode,

              UhradaPoistovne,

              hashcode;

NOTE: In ApplyMap Mapping_ZP has to beloaded before this script an d please do inculde default condition in Applymap Function.

Highlighted
david_balla
New Contributor II

Re: LOAD & SQL with function count disinct

finally it works thank you so much Afros

LIB CONNECT TO 'SQL NCZI_PUZS';


MAINTABLE:

LOAD

DatumPrepustenia,

    Vek,

    left([KodDiagnozyPriPrepusteni],3) as KodDiagnozyPriPrepusteni,

    left([KodPzs],6) as KodPzs,

ApplyMap('Mapping_ZP', Poistovna) as ZdravotnaPoistovna,

    Mid(KodPzs, 7, 3) as Špec,

    TypVykonu,

    PocetOperacnychVykonov,

    [UhradaPoistovne],

    pocetpacientov;

SQL SELECT

DatumPrepustenia,

    Vek,

    KodDiagnozyPriPrepusteni,

    KodPzs,

Poistovna,

    TypVykonu,

    PocetOperacnychVykonov,

    UhradaPoistovne,

     count (distinct hashcode) as pocetpacientov

FROM "datazp_imp".dbo.UstavnaStarostlivost

where year(DatumPrepustenia) in ('2016','2017','2018')

group by DatumPrepustenia,

Vek,KodDiagnozyPriPrepusteni,KodPzs,Poistovna,TypVykonu,PocetOperacnychVykonov,hashcode,UhradaPoistovne,hashcode;

Highlighted
afroz_shaik
New Contributor III

Re: LOAD & SQL with function count disinct

Hi David ,

Mark correct answers if it worked for you, so that for future references it will be helpful.