Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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.