Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jsimmonstx
Contributor

Associating data between tables

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?

3 Replies
lironbaram
Honored Contributor II

Re: Associating data between tables

hi

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

jsimmonstx
Contributor

Re: Associating data between tables

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

Hi,

one solution could be:

QlikCommunity_Thread_230136_Pic1.JPG

QlikCommunity_Thread_230136_Pic5.JPG

QlikCommunity_Thread_230136_Pic6.JPG

QlikCommunity_Thread_230136_Pic2.JPG

QlikCommunity_Thread_230136_Pic3.JPG

QlikCommunity_Thread_230136_Pic4.JPG

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

Community Browser