Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

schivet
New Contributor III

Split Transactions into Period

Hi,

I need to split transactions into different Period (month) depending of the transaction date the starting and ending date. This has to be done during the load of the data (will be used in multiple places).

Well the better way to explain is to show my input and what would be the output!

Input:

Id

Date of Transaction

Starting Date

Ending Date

Amount

1

2016-01-12

2016-01-01

2017-01-01

12 000

2

2016-06-01

2017-01-01

2018-01-01

12 000

3

2016-06-01

2016-01-01

2017-01-01

12 000

4

2016-01-05

2015-06-01

2016-05-01

11 000

5

2016-03-08

2015-01-01

2016-01-01

68 765

Output:

In that case, when the Date of transaction is before or during the first period, it's just a split of the full period divided by the number of month

Id

Date of Transaction

Period

Amount

1

2016-01-12

2016-01

1 000

1

2016-01-12

2016-02

1 000

1

2016-01-12

2016-03

1 000

1

2016-01-12

2016-04

1 000

1

2016-01-12

2016-05

1 000

1

2016-01-12

2016-06

1 000

1

2016-01-12

2016-07

1 000

1

2016-01-12

2016-08

1 000

1

2016-01-12

2016-09

1 000

1

2016-01-12

2016-10

1 000

1

2016-01-12

2016-11

1 000

1

2016-01-12

2016-12

1 000

same here:

Id

Date of Transaction

Period

Amount

2

2016-06-01

2017-01

1 000

2

2016-06-01

2017-02

1 000

2

2016-06-01

2017-03

1 000

2

2016-06-01

2017-04

1 000

2

2016-06-01

2017-05

1 000

2

2016-06-01

2017-06

1 000

2

2016-06-01

2017-07

1 000

2

2016-06-01

2017-08

1 000

2

2016-06-01

2017-09

1 000

2

2016-06-01

2017-10

1 000

2

2016-06-01

2017-11

1 000

2

2016-06-01

2017-12

1 000

A bit more complicated, the date of transaction is after the start of the period but before the end:

Id

Date of Transaction

Period

Amount

3

2016-06-01

2016-06

6 000

3

2016-06-01

2016-07

1 000

3

2016-06-01

2016-08

1 000

3

2016-06-01

2016-09

1 000

3

2016-06-01

2016-10

1 000

3

2016-06-01

2016-11

1 000

3

2016-06-01

2016-12

1 000

of course the full period can be less than 12 months:

Id

Date of Transaction

Period

Amount

4

2016-01-05

2016-01

8 000

4

2016-01-05

2016-02

1 000

4

2016-01-05

2016-03

1 000

4

2016-01-05

2016-04

1 000

in that case, easy, the period is before the date of transaction:

Id

Date of Transaction

Period

Amount

5

2016-03-08

2016-03

68 765

I know that I will multiply the number of lines but that's the requirement

Anyone got an idea.

Thanks a lot

Tags (3)
1 Solution

Accepted Solutions

Re: Split Transactions into Period

May be something like this:

Table:

LOAD Id,

    [Date of Transaction],

    [Starting Date],

    [Ending Date],

    Amount/Count1 as Amount,

    Amount as Amount1,

    Count;

LOAD Id,

    [Date of Transaction],

    [Starting Date],

    [Ending Date],

    ((Year([Ending Date])*12) + Month([Ending Date])) - (((Year([Starting Date])*12)+Month([Starting Date]))) as Count1,

    ((Year([Date of Transaction])*12) + Month([Date of Transaction])) - (((Year([Starting Date])*12)+Month([Starting Date]))) + 1 as Count,

    Amount;

LOAD * Inline [

Id, Date of Transaction, Starting Date, Ending Date, Amount

1, 2016-01-12, 2016-01-01, 2017-01-01, 12000

2, 2017-06-01, 2017-01-01, 2018-01-01, 12000

3, 2016-06-01, 2016-01-01, 2017-01-01, 12000

4, 2016-01-05, 2015-06-01, 2016-05-01, 11000

5, 2016-03-08, 2015-01-01, 2016-01-01, 68765

];

NewTable:

LOAD Id as ID,

  [Date of Transaction],

  Period,

  If(Floor(MonthStart([Date of Transaction])) = Floor(Period), Amount * Count, Amount) as Amount

Where Floor(MonthStart([Date of Transaction])) <= Floor(Period);

LOAD Id,

  [Date of Transaction],

  Amount,

  Count,

  Date(MonthStart([Starting Date], IterNo() - 1), 'YYYY-MM') as Period

Resident Table

While AddMonths([Starting Date], IterNo() - 1) < [Ending Date];

Concatenate(NewTable)

LOAD Id as ID,

  [Date of Transaction],

  Date([Date of Transaction], 'YYYY-MM') as Period,

  Amount1 as Amount

Resident Table

Where Not Exists(ID, Id);

DROP Tables Table;


Capture.PNG

3 Replies

Re: Split Transactions into Period

May be something like this:

Table:

LOAD Id,

    [Date of Transaction],

    [Starting Date],

    [Ending Date],

    Amount/Count1 as Amount,

    Amount as Amount1,

    Count;

LOAD Id,

    [Date of Transaction],

    [Starting Date],

    [Ending Date],

    ((Year([Ending Date])*12) + Month([Ending Date])) - (((Year([Starting Date])*12)+Month([Starting Date]))) as Count1,

    ((Year([Date of Transaction])*12) + Month([Date of Transaction])) - (((Year([Starting Date])*12)+Month([Starting Date]))) + 1 as Count,

    Amount;

LOAD * Inline [

Id, Date of Transaction, Starting Date, Ending Date, Amount

1, 2016-01-12, 2016-01-01, 2017-01-01, 12000

2, 2017-06-01, 2017-01-01, 2018-01-01, 12000

3, 2016-06-01, 2016-01-01, 2017-01-01, 12000

4, 2016-01-05, 2015-06-01, 2016-05-01, 11000

5, 2016-03-08, 2015-01-01, 2016-01-01, 68765

];

NewTable:

LOAD Id as ID,

  [Date of Transaction],

  Period,

  If(Floor(MonthStart([Date of Transaction])) = Floor(Period), Amount * Count, Amount) as Amount

Where Floor(MonthStart([Date of Transaction])) <= Floor(Period);

LOAD Id,

  [Date of Transaction],

  Amount,

  Count,

  Date(MonthStart([Starting Date], IterNo() - 1), 'YYYY-MM') as Period

Resident Table

While AddMonths([Starting Date], IterNo() - 1) < [Ending Date];

Concatenate(NewTable)

LOAD Id as ID,

  [Date of Transaction],

  Date([Date of Transaction], 'YYYY-MM') as Period,

  Amount1 as Amount

Resident Table

Where Not Exists(ID, Id);

DROP Tables Table;


Capture.PNG

Re: Split Transactions into Period

Maybe like this:

Data:

LOAD

  *,

  if([Date of Transaction]> [Ending Date], Amount,

  if(MonthStart([Date of Transaction])<=[Starting Date],Amount/Months,

  if([Date of Transaction]>=Date,

  Amount*(1-Delta +Delta/Months),

  Amount*(Delta)/Months

  )

  )

  ) as New_Amount

  ;

LOAD

  *,

  MonthStart([Ending Date],-IterNo()) as Date

WHILE

  Iterno() <= Months

  ;

LOAD

  Id,

  [Date of Transaction],

  [Starting Date],

  [Ending Date],

  if([Date of Transaction]> [Ending Date],

  1,

  if(MonthStart([Date of Transaction])<=[Starting Date],

  round(([Ending Date]-[Starting Date])/30) ,

  round(([Ending Date]-MonthStart([Date of Transaction]))/30)

  )

  )  as Months,

  if([Date of Transaction]> [Ending Date],

  1,

  if(MonthStart([Date of Transaction])<=[Starting Date],

  1/round(([Ending Date]-[Starting Date])/30),

  round(([Ending Date]-MonthStart([Date of Transaction]))/30) / round(([Ending Date]-[Starting Date])/30)

  )

  )  as Delta,

     Amount

INLINE [

    Id, Date of Transaction, Starting Date, Ending Date, Amount

    1, 2016-01-12, 2016-01-01, 2017-01-01, 12000

    2, 2016-06-01, 2017-01-01, 2018-01-01, 12000

    3, 2016-06-01, 2016-01-01, 2017-01-01, 12000

    4, 2016-01-05, 2015-06-01, 2016-05-01, 11000

    5, 2016-03-08, 2015-01-01, 2016-01-01, 68765

];


talk is cheap, supply exceeds demand
schivet
New Contributor III

Re: Split Transactions into Period

Thank you Sunny and Gysbert! Both solution are working perfectly!

I will vote for you Sunny as you were the first one to answer but once again thank you Gysbert...

Now that I've been working with real figures, it turned out that the requirement is a bit different! We need to apply the weight of the days within the months. In the case of Id 1, the result would be:

   

IdDate Of TransactionPeriodAmountShould be
12015-01-012015-011 000,001 019,18
12015-01-012015-021 000,00920,55
12015-01-012015-031 000,001 019,18
12015-01-012015-041 000,00986,30
12015-01-012015-051 000,001 019,18
12015-01-012015-061 000,00986,30
12015-01-012015-071 000,001 019,18
12015-01-012015-081 000,001 019,18
12015-01-012015-091 000,00986,30
12015-01-012015-101 000,001 019,18
12015-01-012015-111 000,00986,30
12015-01-012015-121 000,001 019,18
Total 12 000,0012 000,00

 

Any idea?

Community Browser