Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

only load first date of every item

hi com,

i want the load script to only load the first date out of several which all have the same characteristics besides the date!


example


date     number     value

2/5          1               5

10/4        1               5

12/5        1               5


so i only want 2/5 to be loaded and not all


hope u can help me

15 Replies
arulsettu
Master III
Master III

first 1 LOAD

     *

from sometable;

it will load only first record

Mark_Little
Luminary
Luminary

Hi

I would be be looking at using the Peek functions. Something like below

Temp:

Load

     *,

     IF(Peek(Number,-1) = Number and Peek(Value,-1),

           0,

          1) as Flag

From...

Noconcatenate

Load

     *

Resident Temp

Where flag = 1;

Drop Table Temp;

Mark

Not applicable
Author

thanks for ur answer but its not exactly what i searched for.

let me explain my example a bit more

i have different articles and locations with different amounts.And then i have a date.

This date i select in delivery and sale date. But due to data structure i must only take the first delivery date because

i want to calc the time in company.

NOCONCATENATE

load articlenumber,location,amount,Date_VD as delivery_date

Resident Vd_TAB

where "typ" ='delivery;

left join

load articlenumber,location,Date_VD as sale_date

Resident Vd_TAB

where "typ" ='sale';

Verweildauer:

Load articlenumber,location,delivery_date,sale_date,

sale_date-delivery_date as diffdate resident temp

where sale_date-delivery_date >=0;

drop table temp;

jsenivifor
Contributor III
Contributor III

Hi,

I hope thhis can help you.

tmp:

LOAD * INLINE [

date1,number,value

10/4/2016,13,5

12/5/2016,11,2

2/3/2016,12,4

];

aux:

first 1 load min(date(date1),'DD/MM/YYYY') as fecha_minima

resident tmp;

let vMin = peek( 'fecha_minima', 0, 'aux' );

datos:

first 1 Load

  date1 as min_date1,

  number as min_number,

  value as min_value

resident tmp

where date1 = '$(vMin)';

Luck!

Not applicable
Author

Hi thx for trying to help me!

I think i dont understand ur code completly. So here my code now. Also so u better understand. I want the first delivery date for a product in a location.

But i get the error at aux that he doesnt know the table:

temp:

NOCONCATENATE

load articlnumber,

     location,

    amount,

     Date_VD as delivery_date

Resident Vd_TAB

where "typ" ='delivery';

left join

load articlenumberr,

     location,

     Date_VD as sale_datum

     Resident Vd_TAB

where "typ" ='salef';

aux:

first 1 load min(date(delivery_date),'DD/MM/YYYY') as date_min

resident tmp;

let vMin = peek('date_min',0,'aux');

Vd:

first 1 Load  articlenumber,

    location,

     delivery_date,

     sale_date,

     sale_date-delivery_date as diffdate resident temp

where sale_date-delivery_date >=0 and delivery_date ='$(vMin)';

drop table temp;

i feel kind of dumb right now, hope u can help!

qlikview979
Specialist
Specialist

Hi Try this,

I have taken date like  "02-may,10-Apr,12-may "

T1:

LOAD date,

     number,

     value

FROM

(ooxml, embedded labels, table is Sheet3);

T2:

load

min(date(date,'MM/YYYY')) as mindate

Resident T1;

qlikview979
Specialist
Specialist

Hi Sorry,

My code will not work

Not applicable
Author

ok

i tried around with some first 1 load in the noconcatenate block at the start. But i seems like noconcatenate and resident and first1 doesn't work together.

qlikview979
Specialist
Specialist

Hi Eric

Here 2/5 means   2-May right.