automatically restructure excel database

[qlikhans]

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

Country	Order 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:

Country	Date	Order Intake Budget January 2017 (EUR)
A	1-1-2017	6.844.467
B	1-1-2017	108.363
C	1-1-2017	25.000
D	1-1-2017	216.503
E	1-1-2017	647.678
A	1-2-2017	7.458.322
B	1-2-2017	110.760
C	1-2-2017	25.011
D	1-2-2017	224.783
E	1-2-2017	657.647

thanks for your input   

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

table1:
CrossTable (AmountHeader, Amount)
LOAD * FROM [https://community.qlik.com/thread/252811] (html, codepage is 1252, embedded labels, table is @1);

Join
LOAD Distinct
    AmountHeader,
    Left(AmountHeader,Index(AmountHeader,' ',-3)-1) as AmountType,
    Date(Date#(Mid(AmountHeader,Index(AmountHeader,' ',-3)+1,Index(AmountHeader,' ',-1)-Index(AmountHeader,' ',-3)-1),'MMMM YYYY'),'D-M-YYYY') as Date,
    TextBetween(AmountHeader,'(',')') as Currency
Resident table1;

hope this helps

regards

Marco

[qlikhans]

Great Marco!

That did the trick

thank you

mfg

Hans