4 Replies Latest reply: Mar 7, 2017 11:09 AM by Robert Winkel RSS

    creating table excel to sql

    Robert Winkel

      I am trying to create a table with an Excel file and a SQL connection.

      What is the data I need to use for the RESIDENT FIELDS?

      Plans is the first tab in the excel file.

       

      LOAD

          InsName,

          ClassCd,

          PBMID,

          Status,

          PharmId,

          InsID

      FROM [lib://Misc Attachemnts (pharmore_rwinkel)/INS-NAME-TYPE.xlsx]

      (ooxml, embedded labels, table is Plans);

       

      FinalTable:

        LOAD Distinct InsID

      //   Resident InsPlans;
      Resident 'INS-NAME-TYPE';


        LET vField = FieldValue('ClassCd', $(i));

           Left Join (FinalTable)

           LOAD InsID,
                     [ClassCd] as [$(vField)]
        //   Resident InsPlans

             Resident

       

           Where [ClassCd] = '$(vField)';

        NEXT

        • Re: creating table excel to sql
          Petter Skjolden

          Your load script snippet doesn't really make much sense. First of all it is no CONNECTION statement there for a SQL table and there is no SQL statement that pulls rows out of a database.

           

          Secondly there is an orphan NEXT that should go with a preceding FOR statement on one of the previous rows in the script.

           

          Both RESIDENT statements that you have marked in red are syntactically in-correct.

           

          The first one has to be like:

           

          Resident [INS-NAME-TYPE];    // the single quotes will not work here

           

          alternatively you could write

           

          Resident "INS-NAME-TYPE";    // the single quotes will not work here

           

           

          The last red statement is missing a reference to a table and that will never work.

            • Re: creating table excel to sql
              Robert Winkel

              This is my complete set of data.

              What I am trying to do is show ClassCd across the top in 9 columns with the corresponding InsID populating the cells.

              This is what it looks like

              This is what I need

               

                 

              PatIDPatStatus133792
              22750I*DAF*DAFDAARFACIGUARMEDI

               

              Thank you

               

              LOAD
                  ClassCd,
                  "Class Type"
              FROM [lib://Misc Attachemnts (pharmore_rwinkel)/INS-NAME-TYPE.xlsx]
              (ooxml, embedded labels, table is [Plan Type]);

              LOAD
                  InsName,
                  ClassCd,
                  PBMID,
                  Status,
                  PharmId,
                  InsID
              FROM [lib://Misc Attachemnts (pharmore_rwinkel)/INS-NAME-TYPE.xlsx]
              (ooxml, embedded labels, table is Plans);


              LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

              LOAD FacID,
                   PatID,
                   Rank,
                   InsID,
                   RxPrimary,
                   RxSecondary,
                   PriceCd,
                   GroupNo,
                   CardIDNo,
                   PersonCode,
                   CardholderName,
                   NoFacFormPriceCd,
                   DefInvoiceGrp,
                   FormularyID;
              SQL SELECT FacID,
                   PatID,
                   Rank,
                   InsID,
                   RxPrimary,
                   RxSecondary,
                   PriceCd,
                   GroupNo,
                   CardIDNo,
                   PersonCode,
                   CardholderName,
                   NoFacFormPriceCd,
                   DefInvoiceGrp,
                   FormularyID
              FROM FwReports.dbo.PatientMOPs

                   Where FacId <> 'FORMS'
                   AND FacId <> 'MSTR'
                   AND FacId <> 'RETAIL'
                   AND FacId <> 'SAMPLE'
                   AND FacId <> 'TEST'
                   AND FacId <> 'TEST#2'
                   AND FacId <> 'TEST2'
                   AND FacId <> 'TEST3'
                   AND FacId <> 'TRLK'
                   AND FacId <> 'stag'
                   And Rank < '100';

               

                FinalTable:

                LOAD Distinct InsID

              Resident [INS-NAME-TYPE];


                LET vField = FieldValue('ClassCd', $(i));

                   Left Join (FinalTable)

                   LOAD InsID,
                             [ClassCd] as [$(vField)]
                   Resident [INS-NAME-TYPE]

                   Where [ClassCd] = '$(vField)';

                NEXT

               

                • Re: creating table excel to sql
                  Petter Skjolden

                  After having looked closely at the load script you made and read your explanation it seems to me that you maybe are over-complicating or using the wrong approach to achieve a pivot. A pivot can be done in the load script but a much better approach is to do the pivot with the help of the Pivot Table component in the UI. That gives you much more flexibility in your visualization since that allows you to pivot out different fields into columns on different tables and changing it on the fly in each table too. Pivoting in the load script leaves you with only one particular set of columns based on one specific field - very static.

                   

                  As a first step I would suggest that the two first loads should result in one common table. This table will connect automatically at the very end of the load script with the third table since they share a common field - named the InsID. This effectively acts like a kind of join. In Qlik this is called an association by key field. The data is kept as separate tables and calculation-wise in the UI construction it will be as if the two tables where really joined because of the associative model.

                   

                  The pivoting you attempt at the very end by looping through all the values of the ClassCd rows is to get the nine columns pivoted out ... let's leave that to be done by the Pivot Table. Trust me you will be much happier once you learn how to do it.

                   

                  So following my preferred approach:

                   

                  1) Delete everything from the line Finaltable: and to the end of your script.

                   

                  2)  The first two tables could be one by using mapping load and ApplyMap():


                  ClassMap:

                  MAPPING LOAD        // a MAPPING table like this disappear automatically at the end of the load script.
                      ClassCd,
                      "Class Type"
                  FROM [lib://Misc Attachemnts (pharmore_rwinkel)/INS-NAME-TYPE.xlsx]
                  (ooxml, embedded labels, table is [Plan Type]);

                   

                  INS-NAME-TYPE:

                  LOAD
                      InsName,
                      ClassCd,

                      ApplyMap('ClassMap',ClassCd) AS "Class Type"

                      PBMID,
                      Status,
                      PharmId,
                      InsID
                  FROM [lib://Misc Attachemnts (pharmore_rwinkel)/INS-NAME-TYPE.xlsx]
                  (ooxml, embedded labels, table is Plans);

                   

                   

                  In the UI in Qlik Sense in your sheet you can create a Pivot Table and place Status in a dimension column and keep the rest of the row label fields as dimension rows. Finally you put InsID as a measure. I guess that it is one unique InsID per unique combination of PatID and Status - right?

                   

                  If you don't get this to work or you find my instructions to hard to understand or follow it would be helpful with some sample data and I could quickly hack together a full sample application that works for you...