Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jerrytan90
New Contributor III

Keep adding N value group by dimensions


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:

IDTransactionNoAccountNo
000010000000020A100
000010000000040A101
000010000000060A102
000010000000080A111
000020000000020A112
000020000000040A113
000030000000020A114
000030000000040A115
000030000000060A116
000030000000080A117

Regards,

Jerry

Tags (1)
1 Solution

Accepted Solutions
Not applicable

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

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;

6 Replies
Not applicable

Re: Keep adding N value group by dimensions

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 ?

jerrytan90
New Contributor III

Re: Re: Keep adding N value group by dimensions

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.

IDTransactionNoAccountNo
000010000000020A100
000010000000040A101
000010000000060A102
000010000000080A111
000020000000020A112
000020000000040A113
000030000000020A114
000030000000040A115
000030000000060A116
000030000000080A117
000040000000020A118
000040000000040A119
000040000000060A120


Thanks and Regards,

Jerry

Not applicable

Re: Keep adding N value group by dimensions

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

Can you please post your source data ?

jerrytan90
New Contributor III

Re: Re: Keep adding N value group by dimensions

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

Not applicable

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

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;

jerrytan90
New Contributor III

Re: Keep adding N value group by dimensions

Great. Thanks dathu.

Community Browser