Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
Not applicable
Author

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;

View solution in original post

6 Replies
Not applicable
Author

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 ?

Anonymous
Not applicable
Author

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
Author

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

Can you please post your source data ?

Anonymous
Not applicable
Author

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
Author

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;

Anonymous
Not applicable
Author

Great. Thanks dathu.