2 Replies Latest reply: Mar 11, 2017 6:15 AM by Hans kleijnen RSS

    automatically restructure excel database

    Hans kleijnen

      Hi all,

      today's challenge.

      I have inherited an excel database. approximate 18000 lines. about 250 columns. Approximate 200 columns contain numeric data, approx 50 columns that could be used as dimensions. Since reporting is coming in monthly, in this format from multiple places I can not change easily the set up of the file overnight.

       

      table below shows some typical headings. No date column is included. For each month for each topic separate columns are created: OI budget, OI intake actual, OI Forecast, Backlog, Revenu budget, revenu realized,, revenu forecast I, etc

       

      CountryOrder Intake Budget January 2017 (EUR)Order Intake Budget February 2017 (EUR)Order Intake Actuals September 2016 (EUR)
      A        6.844.467         7.458.322         8.709.211
      B          108.363           110.760           280.900
      C            25.000             25.011           145.223
      D          216.503           224.783           279.774
      E          647.678           657.647         1.016.776

       

      My idea is to use QV to clean up this file.

       

      Would it be possible to for example have qlikview based on the heading:

      - combine all "order intake budget xxxxxx" into 1 column

      - create an extra date column which is filled with a date (Always 1st day of the month)

      Result:

         

      CountryDateOrder Intake Budget January 2017 (EUR)
      A1-1-2017        6.844.467
      B1-1-2017          108.363
      C1-1-2017            25.000
      D1-1-2017          216.503
      E1-1-2017          647.678
      A1-2-2017        7.458.322
      B1-2-2017          110.760
      C1-2-2017            25.011
      D1-2-2017          224.783
      E1-2-2017          657.647

       

      thanks for your input