topic Re: how to remove last Date from table in QlikView

how to remove last Date from table

Tue, 22 Mar 2016 06:25:53 GMT

Hi Friends

I want to remove last Date records from Table Dynamically

Eg:

Date

01/01/2016

02/01/2016

03/01/2016

Here I need to remove 03/01/2016. I need to do this dynamically why because after some time the date may be change. Guys can u please help me on this

Thanks & Regards

Abi

Re: how to remove last Date from table

arulsettu — Tue, 22 Mar 2016 06:29:32 GMT

have you tried

Max(date)-1

Re: how to remove last Date from table

Kushal_Chawda — Tue, 22 Mar 2016 06:33:01 GMT

Can you tell us what is the purpose of removing last date ? Do you want to achieve it from front end or back end?

Re: how to remove last Date from table

jagan — Tue, 22 Mar 2016 06:36:31 GMT

Hi,

Try like this

Temp:

LOAD

Date,

FROM DataSource;

Left JOin(Temp)

LOAD

Max(Date) AS Date,

1 AS Flag

RESIDENT Temp;

Data:

Noconcatenate

LOAd

RESIDENT Temp

WHERE Flag <> 1;

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.

Re: how to remove last Date from table

Anonymous — Tue, 22 Mar 2016 06:41:27 GMT

max(date)-1

Re: how to remove last Date from table

senpradip007 — Tue, 22 Mar 2016 06:46:25 GMT

Try like:

Temp:

LOAD

Max(Date) as MxDate

FROM DataSource;

let vMaxDate = peek('Date', 0,'Temp');

Drop table Temp;

FinalTab:

Load *

FRM DataSource

Where Date < $(vMaxDate);

Re: how to remove last Date from table

Digvijay_Singh — Tue, 22 Mar 2016 07:00:37 GMT

Try this -

t1:

Load * inline [

Date

01/01/2016

02/01/2016

03/01/2016 ];

Let vLast = FieldValue('Date',NoOfRows('t1'));

NoConcatenate

t2:

Load *

Resident t1

Where Date<>'$(vLast)';

DROP Table t1;

Re: how to remove last Date from table

amit_saini — Tue, 22 Mar 2016 07:07:27 GMT

Hi,

Try:

Max(Date_Field ) as Latest_Date

and later

where Date_Field <> max(Date_Field);

Thanks

Re: how to remove last Date from table

jonathandienst — Tue, 22 Mar 2016 07:29:41 GMT

@Jagan

I think you need:

Data:

Noconcatenate

LOAd

RESIDENT Temp

WHERE Flag <> 1 Or IsNull(Flag);

Re: how to remove last Date from table

tresB — Tue, 22 Mar 2016 07:37:54 GMT

@Jonathan

I guess

....

WHERE Flag <> 1 alone is good enough here. Am I missing something? The Isnull() would be implicit here.

Re: how to remove last Date from table

jonathandienst — Wed, 23 Mar 2016 05:06:58 GMT

I though QV was like SQL here; but I tested this scenario and you are quite correct. Keep on learning