Import result shows values are adding up from multiple codes
Hi All,
This issue is related from yesterday but with a different approach. We created a Gencon Module (transaction Data of client) and the Inventory Module (data from Gencon without the duplicates).
Gencon List has duplicate RU text (Transaction Code) which will be imported to Inventory List eliminating the duplicates and only capturing the RU text with Fully Paid status. Codes with no duplicate and Cancelled status in Gencon will be tagged as Unsold.
Below is the sample of the Gencon module. To forward this GenCon List and create an Inventory List the process we use is import action to eliminate the duplicates.
Below is the expected result after the import process.
Currently below is my result. As you can see the Status column got the wrong result on Transaction ID 422 and also the value for floor area duplicated.
Is there a way for me to capture the Status column for Fully Paid only and also to get the floor area without adding the other items on the GenCon List.
Appreciate the help in advance.
Regards,
Best Answers
-
Follow the steps
Step 1 - Create a dummy list with transaction IDs. Lets call it (General list). It can be a one time set up, either you load from transaction ID line item of GenCon Module or simpy insert the numbers from 1-1000. You can scale it as per your need.
Step 2 - Go to GenCon Module and Insert 6 Line items i.e.
a) Count - Hardcode it to 1
b) Find out Valid Transaction ID - This is to convert text formatted line item into list formatted (General List from step 1)
c) Sum the count - Use SUM & LookUP here. Remember you can split sum & look as there are some known performance issues with the combo This is only for Demo purpose
d) Final Status - Use the logic that you described above. I have written based on what you posted.
e) Final Transaction ID - If Final status is not blank then shows the result
f) Final Flr Area - If Final Status is not blank then shows the results
Step 3 - Import the line items into Inventory Module based on Final Transaction ID, Final Status & Final Flr Area
Hope this helps
Misbah
0 -
Hi Misbah,
Appreciate the help on this. I have one question on this, in the transaction ID line item the value present there came from the Code column in the General List?
In my case I have a Code from the csv file to load in the Gencon List but since there are items with similar code it rejects it during import and only load the unique ones. Also I created the Count column and Find out Valid Transaction ID column in your example and I got a blank result.
Regards,
0 -
If you are trying to load Gencon lists by mapping codes then what is the diff between your gencon list and inventory list, and why the need of inventory list - both will have unique codes.How are you getting two 422's in the Gencon list? You will have to have two diff codes for two 422's but you can have a same display name.
The question that you asked about transaction ID line item - it pulls the display name of the gencon list and not the code of the General list because that's the only way you can have two 422"s in the list. General List is only to change the text formatted line item to List Formatted line item and thence to use SUM & LOOKUP.
Regarding your blank results can you please post a snip of your module.
Thanks,Misbah
0 -
based on what you said
Can your data have 2 fullypaid status for same inventory 422 but with different start and end dates ?
in orther words inventory 422 was booked and fully paid from x date to y date by customer 1 and the
booked and fully paid from y+3 days to z date by customer 2
if above is true you need customer property in your data
0 -
I think you are missing a point here. How is the GenCon List getting created in your solution? As per @Jsdeloria21 he has unique codes in the source file for Gencon List which will reject all the duplicate entries i.e., 422 will be created just once in the list.
I was under the assumption that 422 is just a display name and the underlying code for each 422 is different but that is not the case.
Let us know you thoughts.
Misbah
0 -
Hello @Misbah
1. We must ask the source to provide unique id for the import.
2. Even if there is no unique id on the file, if it is manageable with combination of property. We can import that in a staging module.
3. Apply business rules/logic to select the valid records, create list, hierarchy from the valid records
In my humble opinion, this is a case of selecting valid transactions from a list having duplicate transactions using business rules. I would not recommend complicating it with more imports and actions. Esp, they when they are not scalable and sustainable, what happens when the business rule changes, it should be manageable without changing imports.
Thanks
Arun
0 -
Reason I like spending time with our community is that we get to know how one thing can be achieved in multiple ways. Amazing! All the points that you raised are valid.
Point 1 is the best case scenario.
Point 2 we try to avoid most of the times as it goes against best practices.
Point 3 Your approach relied on IsFirstOccurrence. This has its own limitations, can't use this on a data set more than 10 million transactions. Also there is a performance issue if the data set is more than a million. Now that's debatable if we should use IsFirstOccurrence on Transactional data
Regards,
Misbah
1
Answers
-
Hello @Jsdeloria21 ,
Without complicating it much. The problem statement is filter the right transactions to import.
Create few booleans on the source module to select the correct distinct transactions and procced with import. It should not aggregate and import wrong values.
here is the solution,
Blueprint view,
I have used SUM& LOOKUP for quick modeling, you can avoid it.
Thanks
Arun
0 -
The easiest way is usually the best and from what I can gather, I would recommend fixing the source data to only give you the correct data, whether that is coming from a source file or another model.
Rob
0 -
Hi Misbah,
The difference between Gencon List and Inventory List is that Gencon List is all of their transactional data. For example the 422 code, this code can have multiple transaction from different client. Their Inventory List is the same as the Gencon List except in this list they only filter the codes with with Fully Paid status and Codes with no duplicate whether and tagged it as Unsold. This was the challenge from the beginning because the data that they have is not clean.
Regards,
0 -
I will leave that upto you how are you going to bring duplicate IDs into your list. It can be done in Anaplan but by bypassing the best practices (Combining the properties) which can't be advocated & If and when you do below solution will work for sure. As @rob_marshall highlighted the importance of getting it done within the source system the first priority should be to get it done within the source.
Thanks,
Misbah
0 -
Hello @Jsdeloria21
It is very much possible to select/filter the needed unique records based on the logic you provided.
Thanks
Arun
0 -
Hi Misbah,
With regards to your question 422 can have 3 or more in the Gencon List but they won't have the 2 Fully Paid status. They can have 2 Cancelled status and 1 Fully Paid status. Once the status is Fully Paid 422 will no longer be available in their system as per our client. I think I found a way to manipulate the source data to push to inventory. I will try to do it directly in the source during initial import.
Appreciate the feedback and your time on this.
Regards,
0
