topic Re: work with 2 sources in QlikView

work with 2 sources

Fri, 12 Sep 2014 09:40:41 GMT

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 ?

Re: work with 2 sources

vinay_hg — Fri, 12 Sep 2014 09:54:11 GMT

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

rubenmarin — Fri, 12 Sep 2014 09:58:00 GMT

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;

Re: work with 2 sources

Fri, 12 Sep 2014 10:54:53 GMT

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

rubenmarin — Fri, 12 Sep 2014 11:07:01 GMT

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

Re: work with 2 sources

Fri, 12 Sep 2014 11:27:43 GMT

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

Re: work with 2 sources

rubenmarin — Fri, 12 Sep 2014 11:30:17 GMT

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;

...

Re: work with 2 sources

Fri, 12 Sep 2014 11:56:17 GMT

Dont you mean drop table Temp?

Re: work with 2 sources

rubenmarin — Fri, 12 Sep 2014 12:03:34 GMT

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;

Re: work with 2 sources

Fri, 12 Sep 2014 12:12:08 GMT

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?

Re: work with 2 sources

Fri, 12 Sep 2014 12:14:30 GMT

Ok Thank you very much !

Re: work with 2 sources

rubenmarin — Fri, 12 Sep 2014 12:17:21 GMT

Almost, Personal and Address tables should read from Temp_S1 instead of S2.

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 S2 Where not exist('KeyNr', NrKey);

Followed by

Personal:

Load Keynr, Name,...

Resident Temp_S1;

Address:

Load Keynr, ...

Resident Temp_S1;

drop tables Temp_S1, S2;