Loading only the newest data rows into Qlik

[#1]

Hi,

I have the following data structure in my qvd:

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

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:

Date	Company	CEO	e-mail	Phone number
March 3 2015	Comp B	Name 1	XXX@compB.com	111111111
March 3 2015	Comp D	Name 2	XXX@compD.com	333333333
Jan 2. 2014	Comp E	Name 3	XXX@compE.com	444444444

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

[swuehl · Accepted solution]

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

[#3]

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:

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

And i want to store this table into a qvd:

Date	Company	CEO	e-mail	Phone number
March 3 2015	Comp B	Name 1	XXX@compB.com	111111111
March 3 2015	Comp D	Name 2	XXX@compD.com	333333333
Jan 2. 2014	Comp E	Name 3	XXX@compE.com	444444444

[anbu1984]

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

[swuehl]

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?

[#6]

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

[#7]

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.