Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qlikhans
Contributor

automatically restructure excel database

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   

1 Solution

Accepted Solutions

Re: automatically restructure excel database

Hi,

one solution might be:

QlikCommunity_Thread_252811_Pic1.JPG

QlikCommunity_Thread_252811_Pic2.JPG

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

2 Replies

Re: automatically restructure excel database

Hi,

one solution might be:

QlikCommunity_Thread_252811_Pic1.JPG

QlikCommunity_Thread_252811_Pic2.JPG

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
Contributor

Re: automatically restructure excel database

Great Marco!

That did the trick

thank you

mfg

Hans

Community Browser