Table Help

Fri, 23 Nov 2012 12:20:19 GMT

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

Re: Table Help

fosuzuki — Fri, 23 Nov 2012 15:43:15 GMT

Hi,

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

Re: Table Help

Tue, 27 Nov 2012 10:11:39 GMT

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.

Re: Table Help

fosuzuki — Tue, 27 Nov 2012 20:12:50 GMT

Hi Ted,

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

topic Re: Table Help in QlikView

Table Help

Re: Table Help

Re: Table Help

Re: Table Help