work with 2 sources

[#1]

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 ?

[vinay_hg]

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..

[rubenmarin]

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;

[#4]

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)

😃

[rubenmarin · Accepted solution]

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

[#6]

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

[rubenmarin]

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;

...

[#8]

Dont you mean drop table Temp?

[rubenmarin]

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;

[#10]

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?