Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Joining Flatfile and DB table

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 IDPriority Name
1006x
1007y

Table2 from the flatfile:

PriorityIDSLA_StartSLA_Finish
1006610
10071020
10081430

Whatthe result should look like.

Priority IDPriority NameSLA_StartSLA_Finish
1006x610
1007y1020
1008z1430

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

1 Solution

Accepted Solutions
Not applicable

Re: Joining Flatfile and DB table

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

4 Replies
Not applicable

Re: Joining Flatfile and DB table

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

Not applicable

Re: Joining Flatfile and DB table

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


Not applicable

Re: Joining Flatfile and DB table

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

Not applicable

Re: Joining Flatfile and DB table

Fantastic - First option works like a dream.

Many thanks

Community Browser