Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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.
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
PatID | PatStatus | 1 | 3 | 3 | 7 | 9 | 2 |
22750 | I | *DAF | *DAF | DAAR | FACI | GUAR | MEDI |
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
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...
Great Thank you,
pivot table workout perfectly.