topic Creating date from / date to based on a single date in QlikView

Creating date from / date to based on a single date

Thu, 01 Oct 2015 10:53:32 GMT

Hi, All

I have some data with a balance account per day:

Account No	Currency	Aggrement No	Date of balance	Balance
0060210000	USD	0000002400000	31.12.2013	17 000
0060210000	USD	0000002400000	01.01.2014	17 000
0060210000	USD	0000002400000	02.01.2014	18 000
0060210000	USD	0000002400000	03.01.2014	18 000
0060210000	USD	0000002400000	04.01.2014	18 000
0060210000	USD	0000002400000	05.01.2014	19 000
0060210000	USD	0000002400000	06.01.2014	20 000

My task leave only first rows (by Account No, Currency, Aggrement No, Date of balance), where the balance has changed:

Account No	Currency	Aggrement No	Date of balance	Balance
0060210000	USD	0000002400000	31.12.2013	17 000
0060210000	USD	0000002400000	02.01.2014	18 000
0060210000	USD	0000002400000	05.01.2014	19 000
0060210000	USD	0000002400000	06.01.2014	20 000

Any ideas?

Next step I use

Digvijay_Singh — Thu, 01 Oct 2015 15:48:59 GMT

Check this out -

Dimension - 1-4 fields

Exp - Sum(Balance)

Script -

input:

LOAD [Account No],

Currency,

[Aggrement No],

Date(Date#([Date of balance],'DD.MM.YYYY'),'DD.MM.YYYY') as [Date of balance],

Balance

FROM

(html, codepage is 1252, embedded labels, table is @1);

Output:

Load * where KeepFlag=1;

Load [Account No],

Currency,

[Aggrement No],

if(Balance<>Previous(Balance) or RecNo()=1,1,0) as KeepFlag,

[Date of balance],

Balance

Resident input;

Drop Table input;

swuehl — Thu, 01 Oct 2015 15:54:42 GMT

LOAD [Account No],

Currency,

[Aggrement No],

[Date of balance],

Balance

FROM

(html, codepage is 1252, embedded labels, table is @1)

where Balance <> previous(Balance);