Major Frustration and Problem With Joins

[#1]

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;

[#2]

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

[Clever_Anjos · Accepted solution]

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;

[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

[#5]

Clever,

This helped a LOT.  Thank you for getting me on my Qlikview feet. 

I also want to thank the rest of you for chiming in as well.  Your advice is appreciated.

Thanks,

Joel