Sales History

[Michael_Chappell]

Hi,

We have our current app set up in such a way that any changes that are made to a Customer or an Item will also change the historical data (e.g. if a Customer moves from one buying group to another then the analysis shows that that customer has always been a part of the new buying group). We're looking to duplicate this app and change it so it retains the historical information. Does anyone have an insight into how to do this?

When we first had our training a couple of years ago, our Qlik trainer accidentally started our app this way but later changed it therefore my understanding is just changing how the dimensions are mapped?

Thanks for any and all help,

Michael

[brunobertels]

Hi 

see the qlik help site for incremental load 

incremental load will allow you to load data and store the date in qvd 

then loading new data ( with changes ) will append the data store in the qvd without changing the historical data 

incremental allow to add / add and append / add and modify data 

second way it to have a table with the customer hierachical structure with a date 

Ex 

CustomerID BuyingGroup Date

customer1 group 1  01/01/2021

customer1 group 2 01/06/2021 

and use apply map so that customer 1 will be affct to group 1 until 31/05/2021 and then being affected to group 2 on 01/06/2021 

hope it helps 

[Michael_Chappell]

Hi Brunobertels,

Thank you for your response, and my apologies for the incredibly late reply! I started on this side of the project and then unfortunately had to put it on the backburner, but we're back!

Thank you for your information on Incremental Load, that is useful to know, however it's not how we originally set up the app. Changing the new app to an incremental load would mean that any previous data will not be captured( for example, if Customer 1 moved from Buying Group (BG) 1 to BG2 in September 2021 then that will not be captured moving forward from now). 

Our Customer table is currently linked up to the SalesInvoiceLine. Could it be that this needs to change?

All the best,

Michael

[brunobertels]

Hi Michael 

I understand you need to keep former data as it is , and surely change script with incremental load will overwrite historical data. 

Then what about creating QVD for former data and incermental load for present and futur data , then concatenate all ? 

May be i do not well understand your need ( because of my average english) but let say you have :

table with salesInvoiceLine 

table with customer 

and if you are able to create a table with Customer , Group, and Date of integrating group you may be able  to make it work . 

Not sure then to be more usefull so let me advise to repost a new demand explaining your problem with some exemple of actual behaviour and final output needed 

Regards 

Bruno

[Michael_Chappell]

Hi Bruno,

I think you understood my problem perfectly to be honest, and I believe your solution of creating a QVD with a concatenation would've worked brilliantly.

In the meantime I have taken a look back through previous apps we have made and I noticed where I've been going wrong. The Customer table is loaded first and therefore any changes made to the Customer table are made in the app. What we needed was for the SalesInvoiceLine to be loaded first which then retains the historical data of the Invoices.

Thank you again for all your help, and thank you for your lesson on incremental load. We're having some speed issues for the External Team and an Incremental Load might just be the solution to that problem!

All the best,

Michael