Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

creating table excel to sql

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

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

4 Replies
petter
Partner - Champion III
Partner - Champion III

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.

Not applicable
Author

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

petter
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

Great Thank you,

pivot table workout perfectly.