Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
i have a query on how to change GL codes in a historical QVD.
This is a brief description of what needs to be done...
At the moment all our transactions are coded directly to the current GL code, these are then loaded to QlikView. I want to re-map all the historic transactions to new GL codes
All future transactions will be coded directly to the new codes.
An example of the full code would look like (There are 100's)
Old: 411-52-000-1001
411-52-000-1600
New 411-56-000-1001
411-56-000-1600
So the only part of the code that will change is the 4th and 5th digits. This will be based on a logic concerning the 1st 3 digits.
Any help would be great.
Thanks
Hi Hopkinsc,
Do you have any mapping excel with old GL Codes and New GL codes.if you have directly we can replace it by using
RENAME Function.other wise how getting the New Gl Codes.?
other please provide the test data.
Thanks
SHAIK
HI. Yes, the old/new codes are In excel.
I Am awaiting test data from the client. I was hoping Someone could give me an example before my call with the Client.
Load the QVD and Store it back with the new codes by using IF and string manipulation functions.
Hi Hopkinsc,
according to your give two GL codes i prepared the script, please let me know if any issue.
GLMAPING:
Mapping load * Inline [
OLD GL Code,New GL Code
411-52-000-1001,411-56-000-1001
411-52-000-1600, 411-56-000-1600
];
Actual:
LOAD * Inline [
GL Code,Acual
411-52-000-1001,100
411-52-000-1600,200
];
Actual1:
load *,
ApplyMap('GLMAPING',[GL Code]) AS [Actual Gl Code]
Resident Actual;
DROP Table Actual;
Thanks
SHAIK
thanks both,
Is there a way i can just replace the 4th/5th numbers?
so if the first 3 = 411 then make 4/5th digits '56' etc?
Hey Hopkinsc,
If i understood you correctly you have two excel files old and a new one. If you dont want to use the old one anymore then just replace that with the new one in your data file folder and when you reload your qvw a qvd with new data will be generated that will replace the existing qvd which has the old data.
Wouldn't this work for you?
Or if you want to append the qvd with the old one and update a few records then you need to do incremental load for which you would need a Primary key and a timestamp field.
Hope it helps
Thanks
AJ
Hi, no. the data is extracted from the DB with the codes then stored to a qvds and appended to every day. they have now changed all codes and want historical codes in the qvd changed.
Then I think you would need to use incremental load.
Check out this Cookbook created by Rob Downloads - Rob Wunderlich Qlikview Consulting
Download the zip called qlikview cookbook and go to Cookbook > script > incremental load
Thats really helpful.
or this might work
Create a mapping table which has a key(this is the lookup field) and then the new codes which would be directly pulled from DB from now on everyday.
Then in the table loaded from qvd which gets appended everday do Applymap to get the new codes for all the keys where ever it exists. Drop the old code field and use the new one and then store this in a new qvd and start using that.
Makes sense? or do you see any issues there?