LOAD & SQL with function count disinct

[Anonymous]

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;

[dplr-rn]

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

[Anonymous]

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

[Anil_Babu_Samineni]

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;

[Anonymous]

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

[dplr-rn]

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

[afroz_shaik]

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.

[Anonymous]

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;

[afroz_shaik]

Hi David ,

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