Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Table Help

Hi,

I am currently trying to manipulate a large table to prepare it for calculations within the load script.  It involves duplicating each line of data with the addition of a column with a specific date as the field.  The best way I thought about doing this was by joining a date table to my main table (no links involved).

I have attached an example that works (to a degree)

Accounts:

 

Ref

 

AccntRef

 

Start

 

End

 

Transaction

 

Amount

 

AAA

 

1

 

01/07/2010

 

30/06/2011

 

01/07/2010

 

5000

 

BBB

 

2

 

02/08/2010

 

01/08/2011

 

02/08/2010

 

10000

 

AAA

 

3

 

01/07/2010

 

30/06/2011

 

31/12/2010

 

250

 

BBB

 

4

 

02/08/2010

 

01/08/2011

 

31/12/2010

 

500

 

AAA

 

5

 

01/07/2010

 

30/06/2011

 

15/02/2011

 

100

 

BBB

 

6

 

02/08/2010

 

01/08/2011

 

15/02/2011

 

600

 

AAA

 

7

 

01/07/2011

 

30/06/2012

 

01/07/2011

 

7500

 

BBB

 

8

 

02/08/2011

 

01/08/2012

 

02/08/2011

 

15000

 

AAA

 

9

 

01/07/2011

 

30/06/2012

 

31/12/2011

 

500

 

BBB

 

10

 

02/08/2011

 

01/08/2012

 

31/12/2011

 

1000

 

AAA

 

11

 

01/07/2011

 

30/06/2012

 

15/02/2012

 

600

 

BBB

 

12

 

02/08/2011

 

01/08/2012

 

15/02/2012

 

900

Dates:

 

Date

 

31/01/2011

 

28/02/2011

 

31/03/2011

 

30/04/2011

 

31/05/2011

 

30/06/2011

 

31/07/2011

 

31/08/2011

 

30/09/2011

 

31/10/2011

 

30/11/2011

 

31/12/2011

 

31/01/2012

 

28/02/2012

 

31/03/2012

 

30/04/2012

 

31/05/2012

 

30/06/2012

 

31/07/2012

 

31/08/2012

 

30/09/2012

 

31/10/2012

 

30/11/2012

 

31/12/2012

The attached application gives the result as expected.  The issue is that when I apply this to large data sets; the whole application 'falls over'.   Is there a better way I can do this?

In terms of the calculation I need to look at the 'Amount' and use a combinatuion of 'Start' date and 'Date' (added field) to arrive at my answer.  I have tried using a pivot table in the main application; this falls over as well.

Many thanks

Tags (3)
3 Replies
fosuzuki
Valued Contributor II

Re: Table Help

Hi,

I'm not sure if I understood your requirement. Check the attached sample...

Not applicable

Re: Table Help

Fernando,

I have checked your example; unfortunately it doesnt produce a line for every date.

In the example I posted, every line in the Account data should be duplicated in line with the number of dates going from 31/1/11 right through to 31/12/12.

This is what the result would be if I did a join between the two tables; as there is no 'Date' field in the account table, each line would be duplicated (which is the result I am after).

I just cant get this working with a large data set.

fosuzuki
Valued Contributor II

Re: Table Help

Hi Ted,

I've modified a little bit my sample for clarification. And there was also a bug with the intervalmatch.

Community Browser