topic Re: Re: Keep adding N value group by dimensions in QlikView

Keep adding N value group by dimensions

Anonymous — Wed, 04 Dec 2013 02:13:31 GMT

Hi All,

I want to generate the TransactionNo which is it start from 0000000020 by ID. If there have the same ID, the TransactionNo will keep adding 20. On the other hand, it there have different ID, the TransactionNo will start again from 0000000020. Any idea for the scripting?

The following is the example table for the scenario above:

ID	TransactionNo	AccountNo
00001	0000000020	A100
00001	0000000040	A101
00001	0000000060	A102
00001	0000000080	A111
00002	0000000020	A112
00002	0000000040	A113
00003	0000000020	A114
00003	0000000040	A115
00003	0000000060	A116
00003	0000000080	A117

Regards,

Jerry

Re: Keep adding N value group by dimensions

Wed, 04 Dec 2013 03:21:43 GMT

It seems to be possible, but How do you know how many times you need repeat the loop ?

For example , in the above example, for ID, 00002 we wrote only 2 values? what is is the condition ?

Re: Re: Keep adding N value group by dimensions

Anonymous — Wed, 04 Dec 2013 03:33:57 GMT

Hi dathu,

Thanks for reply. To repeat the loop is based on the ID.

Note: The number of repeat the ID is based on the transactions data. It is not fix. It may be can 4 transaction for ID (00001), 2 transaction for ID (00002) and so on.

For example, it there have 4 transactions for ID (00001), then we need to create 4 transactionNo start from 0000000020 and keep adding 20 for the subsequent which fall under ID is 00001. Next, there have 2 transactions for ID(00002), so we need to create 2 transactionNo again start from 0000000020 and keep adding 20 for the subsequent which fall under ID is 00002. The following ID (00003,00004....) are keep repeat the steps above.

ID	TransactionNo	AccountNo
00001	0000000020	A100
00001	0000000040	A101
00001	0000000060	A102
00001	0000000080	A111
00002	0000000020	A112
00002	0000000040	A113
00003	0000000020	A114
00003	0000000040	A115
00003	0000000060	A116
00003	0000000080	A117
00004	0000000020	A118
00004	0000000040	A119
00004	0000000060	A120

Thanks and Regards,

Jerry

Re: Keep adding N value group by dimensions

Wed, 04 Dec 2013 03:42:42 GMT

So, the no of transactions for each id is already in your Source data or ?

Can you please post your source data ?

Re: Re: Keep adding N value group by dimensions

Anonymous — Wed, 04 Dec 2013 03:53:25 GMT

Hi Dathu,

No. The transactionNo is derive column.

Please find the attached file.

There have 2 sheets which are the source data and the expected result.

Thanks and Regards,

Jerry

Re: Re: Re: Keep adding N value group by dimensions

Wed, 04 Dec 2013 04:14:37 GMT

Hi Tan, Please find the attached file

DATA:

LOAD * , Num(Counter*20 , '0000000000') AS TransActionNo

;

LOAD *, IF( Isnull(Previous(ID)) OR ID <> Previous(ID) , 1 , 1 + Peek(Counter) ) AS Counter

;

LOAD ID,

AccountNo,

[Report MonthYear],

Amount

FROM sample.xlsx (ooxml, embedded labels, table is Source);

//DROP Field Counter;

Re: Keep adding N value group by dimensions

Anonymous — Wed, 04 Dec 2013 05:26:25 GMT

Great. Thanks dathu.