Let’s de-normalize transactional data.
Here is the input:
|1||COOKING BOOKS||12.5||06/07/2013 10:57:10|
|1||MEDICAL BOOKS||120.5||06/07/2013 10:57:10|
|1||TRAVEL BOOKS||10.5||06/07/2013 10:57:10|
|2||FOOD PROCESSOR||12.5||06/07/2013 10:57:10|
|2||INTELLIGENT OVEN||120.5||06/07/2013 10:57:10|
|3||CAR VACUUM||15520.45||06/07/2013 10:57:10|
|3||HOTEL PASS||1550.55||06/07/2013 10:57:10|
|4||ZOO PASS||110.4||06/07/2013 10:57:10|
Here is the output:
The table is quite wide so you can download it as csv.
Here is the mapping overview:
SQ ==> EXP ==> AGGR ==> TGT
In SQ_ORDERS sort by CUSTOMER_ID
In EXP_SetSequenceNumbers create three variables as follow:
- currCustomer_v ==> to_char(CUSTOMER_ID)
- Seq_v ==> IIF(currCustomer_v != prevtCustomer_v , 1 , Seq_v + 1 ). Each time we get a new customer_id the Seq get reset to 1 otherwise it will increment by 1.
- prevtCustomer_v ==>to_char(CUSTOMER_ID)
Make sure that prevtCustomer_v is below currCustomer_v and Seq_v as in the following pic:
In AGG_DenormalizeOrders make sure that the following is set:
- Sorted input is checked.
- Group by is checked for Customer_ID
- Create outputs to house ProductName, ProductPrice and TransactionDate
- For each sequence select the value of seq. For instance, ProductName3 select FIRST( PRODUCT_NAME, seq = 3) etc…
For better understanding please see pic below:
Connect the aggregator to the target.