15 Replies Latest reply: Feb 10, 2017 5:24 PM by Joel Harmon RSS

    Major Frustration and Problem With Joins

    Joel Harmon

      I am not sure why my joins won't work.  Here is what I have.  The error shows as "Table not found PFTC".  Not sure why this is the case when I have it defined in a previous tab, as shown below.  Thanks for the help.  I am new to joins in qlikview:

       

      PFTC:

      LOAD ACTIVITY_ID,

           CD_SERVICE_RESOURCE,

           CHARGE_ITEM_ID,

           CLIENT_BILL_IND,

           CLIENT_ORG_ID,

           PFT_CHARGE_ID,

           PFT_ENCNTR_ID;

      SQL SELECT *

      FROM nhms2.Z_STAGE_PFT_CHARGE

      where ACTIVE_FLAG =1 and ACTIVE_IND=1 

      and charge_item_id in (12669952076,12904771391,12905729988,12806709891,12671441968);

       

      LEFT JOIN ([PFTENC]) load

          PFT_ENCNTR_ID

      Resident PFTC;

       

      CHG:

      LOAD

          ENCNTR_ID,

          CHARGE_ITEM_ID,

          ABN_STATUS_DESC,

          ACTIVITY_DT_TM,

           num(Year(ACTIVITY_DT_TM),'###0') as Activity_Year,

           num(Month(ACTIVITY_DT_TM),'00') as Activity_Month,

           num(Day(ACTIVITY_DT_TM),'00') as Activity_Day,

           If(IsNull(ACTIVITY_DT_TM)=0,'Q' & Ceil(Month(ACTIVITY_DT_TM)/3)) as Activity_Quarter,

           num(DaynumberofYear(ACTIVITY_DT_TM),'00') as Activity_DayofYear,

           Floor(ACTIVITY_DT_TM) as ACTIVITY_DT,

           num(Month(ACTIVITY_DT_TM),'00') * 100 + num(Day(ACTIVITY_DT_TM),'00') as Activity_MonthDay,

          ACTIVITY_TYPE_DESC,

          ATTENDING_PHYS_ID,

          BILL_ITEM_ID,

          CD_ACTIVITY_TYPE,

          CD_CHARGE_TYPE,

          CD_MED_SERVICE,

          CD_TIER_GROUP,

          CHARGE_DESCRIPTION,

          CHARGE_EVENT_ACT_ID,

          CHARGE_EVENT_ID,

          CHARGE_TYPE_DESC,

          COMBINE_IND,

          DEF_BILL_ITEM_ID,

          DEPARTMENT_DESC,

          FIN_NBR,

          GROSS_PRICE,

          INTERFACE_FILE_ID,

          ITEM_PRICE,

          ITEM_QUANTITY,

          LEVEL5_DESC,

          MANUAL_IND,

          MED_NBR,

          MED_SERVICE_DESC,

          OFFSET_CHARGE_ITEM_ID,

          ORD_PHYS_ID,

          ORDER_ID,

          ORGANIZATION_ID,

          PAYOR_TYPE_DESC,

          PERF_LOC_DESC,

          PERF_PHYS_ID,

          POSTED_DT_TM,

          PRICE_SCHED_ID,

          PROCESS_FLG,

          REASON_COMMENT,

          REF_PHYS_ID,

          SECTION_DESC,

          SERVER_PROCESS_FLAG,

          Floor(Date(SERVICE_DT_TM)) as SERVICE_DT,

           num(Year(SERVICE_DT_TM),'###0') as Service_Year,

           num(Month(SERVICE_DT_TM),'00') as Service_Month,

           num(Day(SERVICE_DT_TM),'00') as Service_Day,

           If(IsNull(SERVICE_DT_TM)=0,'Q' & Ceil(Month(SERVICE_DT_TM)/3)) as Service_Quarter,

           num(DaynumberofYear(SERVICE_DT_TM),'00') as Service_DayofYear,

           num(Month(SERVICE_DT_TM),'00') * 100 + num(Day(SERVICE_DT_TM),'00') as Service_MonthDay,

          SUBSECTION_DESC,

          SUSPENSE_RSN_DESC,

          VERIFY_PHYS_ID;

      SQL SELECT *

      FROM dba.v_charge

      where order_id in (3092398827,3092656755,3137437089,3137928261,3165049041,3164842039);

       

      LEFT JOIN ([PFTC]) load

            CHARGE_ITEM_ID

      Resident TEST;

        • Re: Major Frustration and Problem With Joins
          Clever Anjos

          Whats the purpose of this?

          LEFT JOIN ([PFTENC]) load

              PFT_ENCNTR_ID

          Resident PFTC;

            • Re: Major Frustration and Problem With Joins
              Joel Harmon

              Clever,

               

              Like I said, I am new to joins and don't really know what I am doing.  I am really good at SQL, but I can't really wrap my head around how Qlikview does joins.  I am trying to join the pftc table to the pftenc table, which seems to work.  I am then trying to join the CHG table to the pftc and enc table, and I am not having any luck.

               

              The above is not all of my qlikview code.  I can post all of it in the next post I make so it is easier to read in one pane.

               

              This is a sample of the SQL that works and I am trying to convert it to Qlikview:

               

              select *

              from    ( select * from nhms2.z_stage_encounter where ENCNTR_ID in (76551089,76559885,76560999) and  ACTIVE_FLAG=1 and active_ind=1 ) e

                      inner join nhms2.z_stage_pft_encntr pe on e.encntr_id = pe.encntr_id and pe.ACTIVE_FLAG=1 and pe.active_ind=1

                      inner join nhms2.z_stage_pft_charge pc on pe.pft_encntr_id = pc.pft_encntr_id and pc.ACTIVE_FLAG=1 and pc.active_ind=1

                      inner join nhms2.z_stage_charge c on e.ENCNTR_ID=c.ENCNTR_ID and pc.charge_item_id = c.charge_item_id and c.ACTIVE_FLAG=1 and c.active_ind=1

                • Re: Major Frustration and Problem With Joins
                  Joel Harmon

                  Here is the entire body of code.  I didn't know what I was doing when I typed "resident test":

                   

                  ENC:

                  LOAD ENCNTR_ID;

                  SQL SELECT *

                  FROM nhms2.Z_STAGE_ENCOUNTER

                  where ACTIVE_FLAG =1 and ACTIVE_IND=1

                     and ENCNTR_ID in (76551089,76559885,76560999);

                   

                  PFTENC:

                  LOAD ENCNTR_ID,

                    PFT_ENCNTR_ID;

                  SQL SELECT *

                  FROM nhms2.z_stage_pft_encntr

                  where ACTIVE_FLAG =1 and ACTIVE_IND=1

                  and pft_encntr_id in (432750161,432830923,432750161);

                   

                  INNER JOIN ([ENC]) load

                      ENCNTR_ID

                  Resident PFTENC;

                   

                  PFTC:

                  LOAD ACTIVITY_ID,

                       CD_SERVICE_RESOURCE,

                       CHARGE_ITEM_ID,

                       CLIENT_BILL_IND,

                       CLIENT_ORG_ID,

                       PFT_CHARGE_ID,

                       PFT_ENCNTR_ID;

                  SQL SELECT *

                  FROM nhms2.Z_STAGE_PFT_CHARGE

                  where ACTIVE_FLAG =1 and ACTIVE_IND=1

                  and charge_item_id in (12669952076,12904771391,12905729988,12806709891,12671441968);

                   

                  LEFT JOIN ([PFTENC]) load

                      PFT_ENCNTR_ID

                  Resident PFTC;

                   

                  CHG:

                  LOAD

                      ENCNTR_ID,

                      CHARGE_ITEM_ID,

                      ABN_STATUS_DESC,

                      ACTIVITY_DT_TM,

                       num(Year(ACTIVITY_DT_TM),'###0') as Activity_Year,

                       num(Month(ACTIVITY_DT_TM),'00') as Activity_Month,

                       num(Day(ACTIVITY_DT_TM),'00') as Activity_Day,

                       If(IsNull(ACTIVITY_DT_TM)=0,'Q' & Ceil(Month(ACTIVITY_DT_TM)/3)) as Activity_Quarter,

                       num(DaynumberofYear(ACTIVITY_DT_TM),'00') as Activity_DayofYear,

                       Floor(ACTIVITY_DT_TM) as ACTIVITY_DT,

                       num(Month(ACTIVITY_DT_TM),'00') * 100 + num(Day(ACTIVITY_DT_TM),'00') as Activity_MonthDay,

                      ACTIVITY_TYPE_DESC,

                      ATTENDING_PHYS_ID,

                      BILL_ITEM_ID,

                      CD_ACTIVITY_TYPE,

                      CD_CHARGE_TYPE,

                      CD_MED_SERVICE,

                      CD_TIER_GROUP,

                      FIN_NBR,

                      GROSS_PRICE,

                      INTERFACE_FILE_ID,

                      ITEM_PRICE,

                      ITEM_QUANTITY,

                      LEVEL5_DESC,

                      MANUAL_IND,

                      MED_NBR,

                      MED_SERVICE_DESC,

                      OFFSET_CHARGE_ITEM_ID,

                    SQL SELECT *

                  FROM dba.v_charge

                  where order_id in (3092398827,3092656755,3137437089,3137928261,3165049041,3164842039);

                   

                  LEFT JOIN ([PFTC]) load

                        CHARGE_ITEM_ID

                  Resident CHG;

                   

                  LEFT JOIN ([ENC]) load

                        ENCNTR_ID

                  Resident CHG;

                    • Re: Major Frustration and Problem With Joins
                      Joel Harmon

                      One small update.  I made a change to the code to get rid of the Resident Test and change it to Resident CHG and I also tried to do another join to mimic my SQL above.  Weird thing is that this should join the table dba.v_charge (CHG) to the two tables Encounter ENC and to nhms2.Z_STAGE_PFT_CHARGE (PFTC):

                       

                      LEFT JOIN ([PFTC]) load

                            CHARGE_ITEM_ID

                      Resident CHG;

                       

                      LEFT JOIN ([ENC]) load

                            ENCNTR_ID

                      Resident CHG;

                       

                      I now get a Cartesian product and a warning that says:

                       

                      "One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog."

                       

                      However, if you look at my SQL above, you only need to link the CHG table to the encounter table and the pft_charge table.

                       

                      Thanks,

                       

                      Joel

                    • Re: Major Frustration and Problem With Joins
                      Clever Anjos

                      If you´re familiar with SQL and your tables reside on same database I don´t see any problem you make your joins using regular SQL instead of translating it into Qlik

                       

                      You can choose what it is better for your

                        • Re: Major Frustration and Problem With Joins
                          Joel Harmon

                          Because I would like to do this one in Qlik as a learning experience.  At this point, I have a Cartesian product and just need to fix that.  This is being caused here:

                           

                          LEFT JOIN ([PFTC]) load

                                CHARGE_ITEM_ID

                          Resident CHG;

                           

                          LEFT JOIN ([ENC]) load

                                ENCNTR_ID

                          Resident CHG;

                           

                          The SQL equivalent for that part is here:

                           

                          inner join nhms2.z_stage_pft_charge PFTC on pe.pft_encntr_id = PFTC.pft_encntr_id

                          inner join nhms2.z_stage_charge CHG on ENC.ENCNTR_ID=CHG .ENCNTR_ID and PFTC.charge_item_id = CHG .charge_item_id

                           

                          When I run it in SQL, I get the right number of rows.  When I run the qlikview code, I get repeating rows.  My join isn't working in Qlik.  Anyone know why?

                           

                          Thanks,

                           

                          Joel

                            • Re: Major Frustration and Problem With Joins
                              Clever Anjos

                              Maybe this can solve your need (hard to test without your data)

                              ENC:
                              LOAD ENCNTR_ID;
                              SQL SELECT ENCNTR_ID
                              FROM nhms2.Z_STAGE_ENCOUNTER
                              where ACTIVE_FLAG =1 and ACTIVE_IND=1 and ENCNTR_ID in (76551089,76559885,76560999);
                              
                              inner join (ENC)
                              LOAD
                                ENCNTR_ID,
                                PFT_ENCNTR_ID;
                              SQL SELECT *
                              FROM nhms2.z_stage_pft_encntr
                              where ACTIVE_FLAG =1 and ACTIVE_IND=1
                              and pft_encntr_id in (432750161,432830923,432750161);
                              
                              //INNER JOIN ([ENC]) load
                              //  ENCNTR_ID
                              //Resident PFTENC;
                              
                              LEFT JOIN(ENC)
                              LOAD ACTIVITY_ID,
                                  CD_SERVICE_RESOURCE,
                                  CHARGE_ITEM_ID,
                                  CLIENT_BILL_IND,
                                  CLIENT_ORG_ID,
                                  PFT_CHARGE_ID,
                                  PFT_ENCNTR_ID;
                              SQL SELECT ACTIVITY_ID,
                                  CD_SERVICE_RESOURCE,
                                  CHARGE_ITEM_ID,
                                  CLIENT_BILL_IND,
                                  CLIENT_ORG_ID,
                                  PFT_CHARGE_ID,
                                  PFT_ENCNTR_ID
                              FROM nhms2.Z_STAGE_PFT_CHARGE
                              where ACTIVE_FLAG =1 and ACTIVE_IND=1
                              and charge_item_id in (12669952076,12904771391,12905729988,12806709891,12671441968);
                              
                              //LEFT JOIN ([PFTENC]) load
                              //    PFT_ENCNTR_ID
                              //Resident PFTC;
                              
                              LEFT JOIN(ENC)
                              LOAD
                                  ENCNTR_ID,
                                  CHARGE_ITEM_ID,
                                  ABN_STATUS_DESC,
                                  ACTIVITY_DT_TM,
                                  num(Year(ACTIVITY_DT_TM),'###0') as Activity_Year,
                                  num(Month(ACTIVITY_DT_TM),'00') as Activity_Month,
                                  num(Day(ACTIVITY_DT_TM),'00') as Activity_Day,
                                  If(IsNull(ACTIVITY_DT_TM)=0,'Q' & Ceil(Month(ACTIVITY_DT_TM)/3)) as Activity_Quarter,
                                  num(DaynumberofYear(ACTIVITY_DT_TM),'00') as Activity_DayofYear,
                                  Floor(ACTIVITY_DT_TM) as ACTIVITY_DT,
                                  num(Month(ACTIVITY_DT_TM),'00') * 100 + num(Day(ACTIVITY_DT_TM),'00') as Activity_MonthDay,
                                  ACTIVITY_TYPE_DESC,
                                  ATTENDING_PHYS_ID,
                                  BILL_ITEM_ID,
                                  CD_ACTIVITY_TYPE,
                                  CD_CHARGE_TYPE,
                                  CD_MED_SERVICE,
                                  CD_TIER_GROUP,
                                  FIN_NBR,
                                  GROSS_PRICE,
                                  INTERFACE_FILE_ID,
                                  ITEM_PRICE,
                                  ITEM_QUANTITY,
                                  LEVEL5_DESC,
                                  MANUAL_IND,
                                  MED_NBR,
                                  MED_SERVICE_DESC,
                                  OFFSET_CHARGE_ITEM_ID,
                                SQL SELECT *
                              FROM dba.v_charge
                              where order_id in (3092398827,3092656755,3137437089,3137928261,3165049041,3164842039);
                              
                              //LEFT JOIN ([PFTC]) load
                              //      CHARGE_ITEM_ID
                              //Resident CHG;
                              //
                              //LEFT JOIN ([ENC]) load
                              //      ENCNTR_ID
                              //Resident CHG;
                              
                      • Re: Major Frustration and Problem With Joins
                        Jayant Tibhe

                        In addition to Clever's comment -  Whats this -  Resident TEST

                        I am not able to locate TEST table.

                        • Re: Major Frustration and Problem With Joins
                          Sunny Talwar

                          Although I agree with both the comments above me, but in relation to the error you can try this

                           

                          PFTC:

                          NoConcatenate

                          LOAD ACTIVITY_ID,

                              CD_SERVICE_RESOURCE,

                              CHARGE_ITEM_ID,

                              CLIENT_BILL_IND,

                              CLIENT_ORG_ID,

                              PFT_CHARGE_ID,

                              PFT_ENCNTR_ID;

                          SQL SELECT *

                          FROM nhms2.Z_STAGE_PFT_CHARGE

                          where ACTIVE_FLAG =1 and ACTIVE_IND=1

                          and charge_item_id in (12669952076,12904771391,12905729988,12806709891,12671441968);

                           

                          NoConcatenate might be needed if you have another table which contains the same field names. Without the use of NoConcatenate, the script will auto concatenate this to the previous table and won't create a new table with the name PFTC. On the other hand, if you add NoConcatenate, script will force a creation of the table although it has the same field as another table before it.

                          • Re: Major Frustration and Problem With Joins
                            Sachin duganavar

                            HI,

                             

                            Can u attach complete script or qvw file if small bcs not clear frm above script and not able to locate tables like PFTENC and TEST.

                             

                            Or try frm you end like aftr each step put exit script and check wthr tables r getting loaded properly or not.

                             

                            Sachin

                            • Re: Major Frustration and Problem With Joins
                              Peter Cammaert

                              FYI QlikView JOINS and SQL JOINS operate in much the same way but - as you may have guessed already - you write them differently.

                               

                              While a SQL join is selfcontained (everything - including complex JOINS - in a single statement), a QlikView JOIN must happen in discrete steps because of the fact that in every LOAD statement there is only one output table (resident in memory) and only one data source. Compare this to SQL where you also have one result set but each SELECT statement - by way of embedded JOIN or UNION glue logic - can load form different sources.

                              Also, in QlikView you cannot nest LOAD statements, which is a major feature in SQL.

                               

                              On the positive side: JOIN prefixes are the same as in SQL (INNER, OUTER, LEFT, RIGHT) and they also operate in the same way.

                               

                              And one tip: QlikView JOINs become way easier to write if you keep in mind that the SQL Join ... on ... clause is handled in QlikView by

                              • giving fields whose content should match the same name. This is a common table linking technique in QlikView and is also used when throwing two tables together. It happens implicitly.
                              • searches for explicit field content is handled by the WHERE clause.

                               

                              Of course there are a huge amount of possible variations that make the QlikView way flexible, although it may look very rigid at first. It's just written in a different way.

                               

                              Best,

                               

                              Peter