topic Re: Joining Flatfile and DB table in QlikView

Joining Flatfile and DB table

Wed, 25 Jul 2012 07:39:14 GMT

Hi.

Current case is centered around SLA management.

My mission is to create a Mastertable for Priority + SLA Hours.

Currently the DB lacks the necessary hours requirements needed for the calculations in the report.

therefore i am using a flatfile as masterdata record by which i upload to the application.

Result = Two Tables joined by the PriorityID as a key.

Expected result = One table that has all the fields joined by PriorityID.

Table 1 below is what already exists in the db in the priority table.

Table1:

Priority ID	Priority Name
1006	x
1007	y

Table2 from the flatfile:

PriorityID	SLA_Start	SLA_Finish
1006	6	10
1007	10	20
1008	14	30

Whatthe result should look like.

Priority ID	Priority Name	SLA_Start	SLA_Finish
1006	x	6	10
1007	y	10	20
1008	z	14	30

I realize that a join is required however i am not so sure the code for joining a flatfile and DB table how it works.

Any code snippets or advice would be appreciated.

Best,

Brad

Re: Joining Flatfile and DB table

Wed, 25 Jul 2012 07:53:38 GMT

Hi,

You can load and DB table and flat file into Qlikview first. Say the table names are DBTable and FlatFileTable. Then

FinalTable:

Left Join(DBTable)

Load * resident FlatFileTable;

Drop table DBTable;

Drop table FlatFileTable;

Alternatively, you can use mapping load. Check the reference manual for more information.

Regards,

Xue Bin

Re: Joining Flatfile and DB table

Wed, 25 Jul 2012 08:12:43 GMT

Many Thanks Xue Bin!

The Join works well.

However when dropping both tables -

->Drop table DBTable;

->Drop table FlatFileTable;

The newly created table "FinalTable" disappears from data model.

Here is the script below;

Priority:

SQL SELECT ID as PriorityID,
PropertyText as Priority
FROM EasitManagerSuite.dbo."tblCase_Property";

Flatfile:
LOAD PriorityID,
[SLA StartTid],
[SLA Målåtgärdstid]

FROM
[..\Desktop\Qlikview - ÄrendehanteringSystem\SLA Table.xlsx]
(ooxml, embedded labels, table is Sheet1);

PriorityMaster:
Left join(Priority)
Load * resident Flatfile;
DROP Table Priority;
Drop Table Flatfile;

Best,

Brad

Re: Joining Flatfile and DB table

Wed, 25 Jul 2012 08:19:46 GMT

Try this:

Priority:

SQL SELECT ID as PriorityID,
PropertyText as Priority
FROM EasitManagerSuite.dbo."tblCase_Property";

Flatfile:
LOAD PriorityID,
[SLA StartTid],
[SLA Målåtgärdstid]

FROM
[..\Desktop\Qlikview - ÄrendehanteringSystem\SLA Table.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left join(Priority)
Load * resident Flatfile;
Drop Table Flatfile;

I think this also works:

Priority:

SQL SELECT ID as PriorityID,
PropertyText as Priority
FROM EasitManagerSuite.dbo."tblCase_Property";

Join

LOAD PriorityID,
[SLA StartTid],
[SLA Målåtgärdstid]

FROM
[..\Desktop\Qlikview - ÄrendehanteringSystem\SLA Table.xlsx]
(ooxml, embedded labels, table is Sheet1);

Re: Joining Flatfile and DB table

Wed, 25 Jul 2012 08:24:07 GMT

Fantastic - First option works like a dream.

Many thanks