Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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 ?
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
So, the no of transactions for each id is already in your Source data or ?
Can you please post your source data ?
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
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;
Great. Thanks dathu.