Import result shows values are adding up from multiple codes

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

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,

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

karank_*4578

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

@ArunManickam

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

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

@ArunManickam

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

All comments

Follow the steps

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

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

rob_marshall

@Jsdeloria21 ,

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

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?

Regards,

Regarding your blank results can you please post a snip of your module.

Thanks,

Misbah

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,

karank_*4578

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

Hello @Jsdeloria21

It is very much possible to select/filter the needed unique records based on the logic you provided.

Thanks

Arun

@ArunManickam

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

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,

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

Thanks

Arun