Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Creating date from / date to based on a single date

Hi, All

I have some data with a balance account per day:

Account NoCurrencyAggrement NoDate of balanceBalance
0060210000USD000000240000031.12.201317 000
0060210000USD000000240000001.01.201417 000
0060210000USD000000240000002.01.201418 000
0060210000USD000000240000003.01.201418 000
0060210000USD000000240000004.01.201418 000
0060210000USD000000240000005.01.201419 000
0060210000USD000000240000006.01.201420 000

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

Account NoCurrencyAggrement NoDate of balanceBalance
0060210000USD000000240000031.12.201317 000
0060210000USD000000240000002.01.201418 000
0060210000USD000000240000005.01.201419 000
0060210000USD000000240000006.01.201420 000


Any ideas?

Next step I use

2 Replies
Digvijay_Singh
Honored Contributor III

Re: Creating date from / date to based on a single date

Check this out -

Dimension - 1-4 fields

Exp - Sum(Balance)

balance.PNG

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

[https://community.qlik.com/thread/184938]

(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;

MVP
MVP

Re: Creating date from / date to based on a single date

LOAD [Account No],

     Currency,

     [Aggrement No],

     [Date of balance],

     Balance

FROM

[https://community.qlik.com/thread/184938]

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

where Balance <> previous(Balance);

Community Browser