Highlighted
Community Boss

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.

Jsdeloria21_0-1579671765291.png

Below is the expected result after the import process.

Jsdeloria21_1-1579671872495.png

 

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.

Jsdeloria21_3-1579672094527.png

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,

 

13 REPLIES 13
Community Boss

Re: Import result shows values are adding up from multiple codes

@Jsdeloria21 

 

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

Misbah_1-1579685854165.png

 

Misbah_3-1579685931317.png

 

 

Step 3 - Import the line items into Inventory Module based on Final Transaction ID, Final Status & Final Flr Area

Misbah_0-1579685585776.png

Hope this helps

Misbah

Highlighted
Community Boss

Re: Import result shows values are adding up from multiple codes

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,

Highlighted
Community Boss

Re: Import result shows values are adding up from multiple codes

@Jsdeloria21 

 

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

Highlighted
Community Boss

Re: Import result shows values are adding up from multiple codes

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,

Highlighted
Master Anaplanner/Community Boss

Re: Import result shows values are adding up from multiple codes

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

Highlighted
Community Boss

Re: Import result shows values are adding up from multiple codes

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,

Highlighted
Community Boss

Re: Import result shows values are adding up from multiple codes

@Jsdeloria21 

 

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

Highlighted
Community Boss

Re: Import result shows values are adding up from multiple codes

Hello @Jsdeloria21 

 

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

 

Thanks

Arun

Highlighted
Community Boss

Re: Import result shows values are adding up from multiple codes

@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