Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Loading only the newest data rows into Qlik

Hi,

I have the following data structure in my qvd:

DateCompanyCEOe-mailPhone number
Jan 2. 2014Comp AName 1XXX@compA.com000000000
March 3 2015Comp BName 1XXX@compB.com111111111
Jan 2. 2014Comp CName 2XXX@compC.com222222222
March 3 2015Comp DName 2XXX@compD.com333333333
Jan 2. 2014Comp EName 3XXX@compE.com444444444

I would like to make a script that updates the company, e-mail, and phone number by loading only the newest information for Name 1 and 2, but keeps the information about Name 3 since i do not have any updates information about this person.

The table i would like looks like this:

DateCompanyCEOe-mailPhone number
March 3 2015Comp BName 1XXX@compB.com111111111
March 3 2015Comp DName 2XXX@compD.com333333333
Jan 2. 2014Comp EName 3XXX@compE.com444444444

Any suggestions of an easy way to do this in the script?

1 Solution

Accepted Solutions
MVP
MVP

Re: Loading only the newest data rows into Qlik

Your key is field CEO?

If your data in QVD is already ordered by Date asc, just try a where not exist clause:

LOAD * FROM File.qvd (qvd)

WHERE not exists (CEO);

edit: Ah, of course it needs to come in sorted descending by Date. I assume it does not.

But you can load your resident table ordered correctly (take care not to auto-concatenate:

T1:

LOAD Date as TmpDate, Company, CEO, [e-mail], [Phone number] FROM File.qvd (qvd);

T2:

NOCONCATENATE

LOAD TmpDate as Date, Company, CEO, [email], [Phone number] RESIDENT T1

WHERE NOT EXISTS(Date, TmpDate)

ORDER BY Date desc;

drop table T1;

I think it was too late.., you need to alias the CEO instead of the Date (still order by Date).

6 Replies
MVP
MVP

Re: Loading only the newest data rows into Qlik

Your key is field CEO?

If your data in QVD is already ordered by Date asc, just try a where not exist clause:

LOAD * FROM File.qvd (qvd)

WHERE not exists (CEO);

edit: Ah, of course it needs to come in sorted descending by Date. I assume it does not.

But you can load your resident table ordered correctly (take care not to auto-concatenate:

T1:

LOAD Date as TmpDate, Company, CEO, [e-mail], [Phone number] FROM File.qvd (qvd);

T2:

NOCONCATENATE

LOAD TmpDate as Date, Company, CEO, [email], [Phone number] RESIDENT T1

WHERE NOT EXISTS(Date, TmpDate)

ORDER BY Date desc;

drop table T1;

I think it was too late.., you need to alias the CEO instead of the Date (still order by Date).

Not applicable

Re: Loading only the newest data rows into Qlik

Hi,

Thanks for the reply,

I have arranged so that my qvd shows all rows sorted by date descending.

However, the "Where not exists (CEO)" does not remove names with the old dates when i load.

Any suggestions?

The present order of my qvd with descending dates:

DateCompanyCEOe-mailPhone number
March 3 2015Comp DName 2XXX@compD.com000000000
March 3 2015Comp BName 1XXX@compB.com111111111
Jan 2. 2014Comp CName 2XXX@compC.com222222222
Jan 2. 2014Comp AName 1XXX@compA.com333333333
Jan 2. 2014Comp EName 3XXX@compE.com444444444

And i want to store this table into a qvd:

DateCompanyCEOe-mailPhone number
March 3 2015Comp BName 1XXX@compB.com111111111
March 3 2015Comp DName 2XXX@compD.com333333333
Jan 2. 2014Comp EName 3XXX@compE.com444444444
anbu1984
Honored Contributor III

Re: Loading only the newest data rows into Qlik

Can you post the script you tried?

Load * Inline [
Date,Company,CEO,e-mail,Phone number
March 3 2015,Comp D,Name 2,XXX@compD.com,000000000
March 3 2015,Comp B,Name 1,XXX@compB.com,111111111
Jan 2. 2014,Comp C,Name 2,XXX@compC.com,222222222
Jan 2. 2014,Comp A,Name 1,XXX@compA.com,333333333
Jan 2. 2014,Comp E,Name 3,XXX@compE.com,444444444 ]

Where Not Exists(CEO);

MVP
MVP

Re: Loading only the newest data rows into Qlik

Knut, exists() checks against all previously (and within the current load) loaded field values.

If you loaded another table with CEO before, this might interfere.

But it should not load in all duplicate lines for CEO when loading your address table.

As Anbu suggested, could you post your script or a small sample QVW?

Not applicable

Re: Loading only the newest data rows into Qlik

It seems this is not necessary!

I organized my script to transfrom and store the sorted table in a qvd first, and then load only the data I wanted from this qvd to create the data table i wanted to show in qlik.

Consequently, forgetting to drop the first table interferred when I tried to load only the newest data the second time.

Thanks for your help!!

Not applicable

Re: Loading only the newest data rows into Qlik

Hi knuterik.thorsen ,

Try like this:

Concatenate two table

Like

Table A:

key,

A,B from table;

Concatenate

Table B : 

key,

A, B

from table1

where not exist key;

Ramya.

Community Browser