8 Replies Latest reply: Nov 8, 2018 3:57 PM by Afroz Shaik RSS

    LOAD & SQL with function count disinct

    David Balla

      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;

        • Re: LOAD & SQL with function count disinct
          Dilip Ranjith

          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

          • Re: LOAD & SQL with function count disinct
            David Balla

            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

              • Re: LOAD & SQL with function count disinct
                Dilip Ranjith

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

              • Re: LOAD & SQL with function count disinct
                Anil 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;

                • Re: LOAD & SQL with function count disinct
                  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.

                  • Re: LOAD & SQL with function count disinct
                    David Balla

                    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;