Re-Mapping of historical codes

[hopkinsc]

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 4^th and 5^th digits. This will be based on a logic concerning the 1^st 3 digits.

Any help would be great.

Thanks

[Anonymous]

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

[hopkinsc]

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.

[#4]

Load the QVD and Store it back with the new codes by using IF and string manipulation functions.

[Anonymous]

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

[hopkinsc]

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?

[#7]

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

[hopkinsc]

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.

[#9]

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?