topic Re: 100 oldest records in QlikView

100 oldest records

Mon, 30 Sep 2013 10:27:40 GMT

My problem in a nutshell is:

I load data from an excel file every monday morning.

The file consist of ex. a recordnumber and a recorddate.

the excelfile builds op/accumulate data every day.

the excelfile start every time at 1. january so every time I load data there will be duplicates which I dont want.

I only want to load the new records - not the ones from last week.

How do i work around not loading duplicates.???

secondly i want to get the 100 oldest records every monday and the 100 oldest records must not be the same next monday

so I nedd to set some kind of flag?????

Re: 100 oldest records

Mon, 21 Oct 2013 07:03:34 GMT

Do you have a sample you could share?

Re: 100 oldest records

Mon, 21 Oct 2013 07:07:20 GMT

Hi Thomas

Sorry ☺

Fra: thomas jensen

Sendt: 21. oktober 2013 09:05

Til: Schou, Hans Henrik Søren (SSC009)

Emne: Re: - 100 oldest records

QlikCommunity<http://community.qlik.com/>

100 oldest records

reply from thomas jensen<http://community.qlik.com/people/TJETJETJE?et=watches.email.thread> in App Development - View the full discussion<http://community.qlik.com/message/409154?et=watches.email.thread#409154>

Re: 100 oldest records

Mon, 21 Oct 2013 07:11:10 GMT

Ahh okay - I dont know which datafields you have in your excel, but for the incremental load, you could maybe do something on maxid or date?

Make date as a variable where it takes getdate()-7 and then only load data where createdate > vLastWeekData

Or you could do this on an ID.

DOH - Didnt read that you had recordnumber/recorddate - But then it should be possible to avoid duplicates

Re: 100 oldest records

Mon, 21 Oct 2013 07:16:13 GMT

Det kan godt være mit engelske ikke er så godt, så du får den lige på dansk.

Jeg har nogle data fra et excelark hvoraf jeg filtrere de 100 ældste sager fra.

Så vil jeg godt gemme QVW filen og så næste gang jeg loader excelfilen er der flere data + de samme som sidst
d.v.s. så overskriver jeg jo de "100 gamle".

Efter at have loaded nye data plus de gamle skal jeg filtrere de 100 ældste, - men det må ikke være de samme som de forrige.

Så det skal nok være noget med et incremental load og muligheden for at sette et flag på dem som er brugt tidligere.

Regnearket har ikke unikke post som ID eller et datostempel desværre, - måske en umulig opgave.

Jeg må se at få data fra en database istedet?

Det er vel ikke helt korrekt at skrive på dansk her - findes der ikke et dansk forum?

Re: 100 oldest records

Mon, 21 Oct 2013 07:20:38 GMT

In order not to load duplicate values you might use a WHERE NOT EXISTS(RECORDNUMBER) This is if RecordNumber works as a PK. If not, you can use RecordDate.. Other posibility could be storing in a variable (vDate) always the first day of the week, so you can use something like:

LOAD * FROM XXX

WHERE RecordDate>= $(vDate)

For the 100 oldest values, yes, you can use a variable to flag previously oldest records. Once you get this, you can sort your table and extract the 100 oldest values that are not flagged.

Re: 100 oldest records

Mon, 21 Oct 2013 07:22:46 GMT

Ho Soren,

You have many ways to do it :

- Load file by increment : load the recorddate (if it is relevant) according to the week you want to load (WHERE week(recorddate) = week(today())

- Use the WHILE NOT EXISTS(Recordid). You don't have to worry about date with this

For the 100 oldest record, use recno() to give a numbner to your new records from the week and load it by recorddate ascending. Number from 1 to 100 will give you the 100 oldest

Best regards

Chris

Re: 100 oldest records

Mon, 21 Oct 2013 07:36:37 GMT

I understand your workthru - but my problem is af the first load I find the 100 oldest by setting a flag, - then

I do a incremental load, and that overwrites the flag set to the 100 oldest???

Re: 100 oldest records

Mon, 21 Oct 2013 07:53:33 GMT

I´m sure there will be better ways to do this, but maeby you can try:

1º Create a sorted resident table where you load all the fields that are currently not flagged.

2º While creating this new table you can add the Flag field and flag only the 100 oldest values (rowno() should work)

3º Concatenate the original table with the new one so you can use the Where not Exists (PK)