topic Re: How to create 'From' and 'Till' dates from a single date field ? in QlikView

How to create 'From' and 'Till' dates from a single date field ?

harendra_singh — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I want to know how to create a snapshot i.e., From and till from a single date field. For example, An inventory has a product 'X' cost xyz upto this date and changes after this date.

Items	Price	Date
Apple	10	05/01/2017
Banana	20	10/01/2017
Apple	15	20/01/2017
Guava	30	17/01//2017
Guava	40	25/01/2017

I need this as a result:

Itemss	Price	From	Till
Apple	10	05/01/2017	19/01/2017
Apple	15	20/01/2017
Banana	20	10/01/2017
Guava	30	17/01/2017	24/01/2017
Guava	40	25/01/2017

Any help would be helpful. Thanks anyway

Re: How to create 'From' and 'Till' dates from a single date field ?

effinty2112 — Wed, 30 Aug 2017 10:23:18 GMT

Hi Harendra,

Try:

Data:

LOAD * INLINE [

Products, Price, Date

Apple, 10, 05/01/2017

Banana, 20, 10/01/2017

Apple, 15, 20/01/2017

Guava, 30, 17/01/2017

Guava, 40, 25/01/2017

];

OrderedData:

LOAD

Products,

Price,

Date as From,

if(Products = Peek(Products) , Date(Peek(From)-1), Date(Today())) as To

Resident Data Order by Products, Date desc;

DROP Table Data;

Giving:

Products	From	To	Price
Apple	05/01/2017	19/01/2017	10
Apple	20/01/2017	30/08/2017	15
Banana	10/01/2017	30/08/2017	20
Guava	17/01/2017	24/01/2017	30
Guava	25/01/2017	30/08/2017	40

If you prefer you can get

Products	From	To	Price
Apple	05/01/2017	19/01/2017	10
Apple	20/01/2017		15
Banana	10/01/2017		20
Guava	17/01/2017	24/01/2017	30
Guava	25/01/2017		40

by changing the line of script that defines the field To:

if(Products = Peek(Products) , Date(Peek(From)-1)) as To

cheers

Andrew

Re: How to create 'From' and 'Till' dates from a single date field ?

harendra_singh — Wed, 30 Aug 2017 10:32:56 GMT

Thanks Andrew

Re: How to create 'From' and 'Till' dates from a single date field ?

its_anandrjs — Wed, 30 Aug 2017 10:36:49 GMT

Create your table this ways and use PEEK and PREVIOUS, Order By

Source:
LOAD Products, Price, Date(Date#(Date,'DD/MM/YYYY')) as Date;
LOAD * Inline
[
Products, Price,     Date
Apple,     10, 05/01/2017
Banana,    20, 10/01/2017
Apple,     15, 20/01/2017
Guava,     30, 17/01/2017
Guava,     40, 25/01/2017
];

NoConcatenate
Temp1:
LOAD
Products,
Price,
Date as From_Date,
if(Products = Peek(Products),Date(Previous(Date)-1),'') as To_Date
Resident Source
Order By Products,Date desc;
DROP Table Source;

NoConcatenate
Final:
LOAD * Resident Temp1 Order By Price;
DROP Table Temp1;

EXIT Script;

Re: How to create 'From' and 'Till' dates from a single date field ?

harendra_singh — Wed, 30 Aug 2017 10:52:20 GMT

Thanks Anand its very helpful...