Solved: Re: Major Frustration and Problem With Joins - Qlik Community

Report Inappropriate Content · ‎2017-02-09

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;

Clever_Anjos · ‎2017-02-10

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;

View solution in original post

Clever_Anjos · ‎2017-02-09

Whats the purpose of this?

LEFT JOIN ([PFTENC]) load

PFT_ENCNTR_ID

Resident PFTC;

jayanttibhe · ‎2017-02-09

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

I am not able to locate TEST table.

sunny_talwar · ‎2017-02-09

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.

sdmech81 · ‎2017-02-10

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

Report Inappropriate Content · ‎2017-02-10

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

Report Inappropriate Content · ‎2017-02-10

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;

Report Inappropriate Content · ‎2017-02-10

Hi Sachin,

Entire script is attached to the response to Clever.

Thanks,

Joel

Report Inappropriate Content · ‎2017-02-10

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

Clever_Anjos · ‎2017-02-10

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