topic Re: Associating data between tables in QlikView

Associating data between tables

Anonymous — Tue, 23 Aug 2016 18:48:50 GMT

I've got a table (Table1) that looks like so:

DEPT, Clinic, MEPRSGroup

where MEPRSGroup is a comma-delimited string.

I've got another table (Table2) that has a structure like so:

MEPRS MyDate Value1 Value2

I want to group Table2 by MyDate, where MEPRS is contained in Table1.MEPRSGroup, and associate the record in table2 with the Table1.ClinicName and Table1.Department. So, Table1 contains the something like the following:

Dept1, Clinic1, "Meprs1,Meprs2,Meprs3"

And Table2 contains something like the following:

Meprs1, 01/01/2016, 1, 2

Meprs2, 01/01/2016, 5, 10

I want Table2 to have the following:

01/01/2016, 6, 12, Dept1, Clinic1

How would I go about doing that?

Re: Associating data between tables

lironbaram — Tue, 23 Aug 2016 19:49:16 GMT

in your load script of table one

you can use subfield function

the script should look like this :

load DEPT,Clinic, subfield(MEPRSGroup,',') As MEPRS

from Your source

this will create a row for each value in the MEPRSGroup field ,

then by using left join with Table2 you can get the result you want

Re: Associating data between tables

Anonymous — Wed, 24 Aug 2016 16:02:12 GMT

So, I returned all of the LOAD statements to their most basic content, and I'm STILL getting the "loop detected" warning and I'm STILL getting he object memory error for the table object. I even rebooted my machine trying to solve that.

I can't find anything resembling a loop in this code (and before I did what you said to do, I wasn't having this problem) :

PC_Clinics:
LOAD Department, MEPRS4, ClinicName
FROM PC_Clinic_Table.qvd (qvd)
WHERE (ClinicName <> 'SRP' AND ClinicName <> 'PEDS SUBS' AND ClinicName <> 'IMC SPECS');

DailyF2F_0:
LOAD Week_End_Dt AS Week_End_Date
,ClinicName
,[Clinic-Weekly Avail Appt Goal] AS StdValue
FROM [excel file.xlsx] (ooxml, embedded labels, table is [Clinic Weekly Avail Appt])
WHERE (ClinicName <> 'SRP' AND ClinicName <> 'PEDS SUBS' AND ClinicName <> 'IMC SPECS');

TOCW_0:
LOAD MEPRS4
,Week_End_Date
,SUM(IF(ISNULL([24HR_Num]),0,[24HR_Num])) AS [24HR_Num]
,SUM(IF(ISNULL([24HR_Den]),0,[24HR_Den])) AS [24HR_Den]
,SUM(IF(ISNULL(FTR_Num),0, FTR_Num)) AS FTR_Num
,SUM(IF(ISNULL(FTR_Den),0, FTR_Den)) AS FTR_Den
,SUM(IF(ISNULL(SPEC_Num),0, SPEC_Num)) AS SPEC_Num
,SUM(IF(ISNULL(SPEC_Den),0, SPEC_Den)) AS SPEC_Den
,SUM(IF(ISNULL(WELL_Num),0, WELL_Num)) AS WELL_Num
,SUM(IF(ISNULL(WELL_Den),0, WELL_Den)) AS WELL_Den
FROM TOC_3rdNext_W.qvd (qvd)
WHERE MEPRS4 <> 'BDAA' and MEPRS4 <> 'BAAA'
GROUP BY MEPRS4, Week_End_Date;

Re: Associating data between tables

MarcoWedel — Wed, 24 Aug 2016 23:59:38 GMT

Hi,

one solution could be:

Table1:
LOAD * INLINE [
    DEPT, Clinic, MEPRSGroup
    Dept1, Clinic1, "Meprs1,Meprs2,Meprs3"
];

Table2:
LOAD * INLINE [
    MEPRS, MyDate, Value1, Value2
    Meprs1, 01/01/2016, 1, 2
    Meprs2, 01/01/2016, 5, 10
];

tabLink:
LOAD Distinct
     MEPRSGroup,
     SubField(MEPRSGroup,',') as MEPRS
Resident Table1;

hope this helps

regards

Marco