Transforming an accounting file into an usable file - Help

[Hakim-A]

Hello Community,

I have an excel file I'd like to transform because the original format is hardly workable.

To make it short it looks like an accounting file meaning some rows represent totals + sub-totals and blank values need to be copied from above.

What I need to do basically is to copy down missing values for labels and remove totals and sub totals.

I've attached an excel file with 2 sheets : original file and needed format.

Rows in red need to be removed from the file (they represent sub totals and totals for each category)  and the ones in green added/copied from above.

First I need to copy/duplicate the values from columns A to E to fill the blanks

And then remove all red rows in the scripting of Qlik.

Could you help me achieve that ?

Don't mind about numbers in the file it's only about formatting the file and removing totals sub totals and sub sub totals 🙂

Thank you

[ArnadoSandoval]

Hi @Hakim-A 

It is an accounting report, I do not know its technical name at the moment, the best way to approach this challenges is finding out the source of the report, liaise with its developers and implement a clean-stable application with QlikSense, the solution I propose here returns the results you are expecting but it will be a fragile solution in the long run (as I explain later); first of all, you need to define the Business Rules to produce the expected results, I list them below; these rules focus on creating a new field, Keep, with two possible values: k = keep, i = ignore and based on the example file you provided.

Business Rules:

1. You keep all the rows where the Account Name is in CAPITAL letters.
2. Some Account Names are not fully capitalized, their names start with capital letter but near the end of their name they switched to lowercase or CamelCase, like: "CALYX - ACENSI - GiBa" or "CONSULTANT EXPERT DBA LaLb"; We keep the rows where the starting text is in Capital letters.
3. If the previous rule fails, we keep the row if the Account Name of the previous row is in full capital letters.
4. There are a bunch of rows at the end of the report we need to keep, they are standing by themselves, with empty rows before and after them, we select them by reading the data from bottom to top.
5. The final rule is to roll down some fields value, which is easy.

 WARNINGS:

1. This solution depends in application's data that could be changed by the end users; some of the account names include text resembling daily operational info, which easily could break the rules written above.
2. I strongly suggest contacting the developers of the GL Reports and workout with them the rules they are using to generate such report.

Attachments:

• Your Excel files with some notes.
• The solution 50.GL_Excel

Hope this helps,

[Hakim-A]

Thank you