Joining Flatfile and DB table

[#1]

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

[#2]

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

[#3]

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

[#4 · Accepted solution]

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);

[#5]

Fantastic - First option works like a dream.

Many thanks