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

work with 2 sources

Hi guys,

I want to work with 2 Sources in Qlikview, well thats the strenght of QV I think, but I dont know, how to manage that:

I have 2 sources like this:

S1:

KeyNr,

LName,

FName,

BDate,

Addressname,

AddressNr,

...;

SQL SELECT * FROM XDB;

S2:

NrKey,

Name,

FirstName,

BDay,

Adresse // (-> = Adressname+Nr)

...;

SQL SELECT * FROM YDB;

I have following data:

in S1(XDB) are old data from 2010- June2014

in S2(YDB) are newer data with active people from 2010 - today..

____

I want to split this in 2 types/table: Person and Adress

How can I realize that in Script ?

Tags (2)
1 Solution

Accepted Solutions

Re: work with 2 sources

Assuming that KeyNr can be used to check if data exists, you can use something like:

TempData:

NrKey,

...;

SQL SELECT * FROM YDB;

S1:

KeyNr,

...;

SQL SELECT * FROM XDB;

Concatenate (TempData)

LOAD

KeyNr as NrKey,

...

Resident S1 where not exists('NrKey', KeyNr);

DROP Table S1;

...

Edit: note that I change the order tables were read from SQL, so we load all records from S2 and only those not previously loaded from S1

11 Replies
vinay_hg
Contributor II

Re: work with 2 sources

hi.. it would be helpful if you let us know, u want to concat and show all details or is it on demand data share of old and new data..

Re: work with 2 sources

You can concatenate both tables withalias so the fields will be the same:

TempData:

KeyNr,

Addressname & AddressNr as Adresse

...;

Concatenate (TempData)

LOAD

NrKey as KeyNr,

...;

SQL SELECT * FROM YDB;

Then you can divide data in 2 tables leaving Adresse or the field you want as link in both tables.

Persons:

LOAD Adresse,

     LName...

Resident TempData;

Address:

LOAD Adresse,

     Addressname...

Resident TempData;

DROP Table TempData;

Not applicable

Re: work with 2 sources

Right,

But is it possible, to say that I, just want the data of S2(YDB) if it exist, if not- so from S1 ? Like Address,..

Now it shows me 2 same lines (1 from S1 with A& B as Adress, 2. from S2)

=)

Re: work with 2 sources

Assuming that KeyNr can be used to check if data exists, you can use something like:

TempData:

NrKey,

...;

SQL SELECT * FROM YDB;

S1:

KeyNr,

...;

SQL SELECT * FROM XDB;

Concatenate (TempData)

LOAD

KeyNr as NrKey,

...

Resident S1 where not exists('NrKey', KeyNr);

DROP Table S1;

...

Edit: note that I change the order tables were read from SQL, so we load all records from S2 and only those not previously loaded from S1

Not applicable

Re: work with 2 sources

Well, after SQL.. FROM DB I cant use where not exists..

Re: work with 2 sources

Check my previous post:

TempData:

NrKey,

...;

SQL SELECT * FROM YDB;

S1:

KeyNr,

...;

SQL SELECT * FROM XDB;

Concatenate (TempData)

LOAD

KeyNr as NrKey,

...

Resident S1 where not exists('NrKey', KeyNr);

DROP Table S1;

...

Not applicable

Re: work with 2 sources

Dont you mean drop table Temp?

Re: work with 2 sources

That code was to be inserted before loading Person and Address tables, after loading these tables you can drop Temp table.

S1 Table has to be dropped too, this can be done after concatenate S2 Table on Temp table or when Temp table is dropped.

Full code will be:

TempData:

NrKey,

...;

SQL SELECT * FROM YDB;

S1:

KeyNr,

...;

SQL SELECT * FROM XDB;

Concatenate (TempData)

LOAD

KeyNr as NrKey,

...

Resident S1 where not exists('NrKey', KeyNr);

DROP Table S1;

Persons:

LOAD Adresse,

     LName...

Resident TempData;

Address:

LOAD Adresse,

     Addressname...

Resident TempData;

DROP Table TempData;

Not applicable

Re: work with 2 sources

So all in all it looks like this:

Temp_S1: //Add_data

Load NrKey...

SQL SELECT * FROM YDB

S2: //Basic data

Load KeyNr...

SQL SELECT * FROM XDB

Concatenate (Temp_S1)

Load NrKey as Keynr...

Resident Temp_S1 Where not exist('KeyNr', NrKey);

Followed by

Personal:

Load Keynr, Name,...

Resident S2;

Address:

Load Keynr, ...

Resident S2;

after all -> drop table Temp_S1 & S2

Am I right?

Community Browser