topic Need to combine rows in Excel files in QlikView

Need to combine rows in Excel files

Thu, 10 Mar 2011 22:58:09 GMT

Hello,

I am new to working with Qlikview and I am having problems with getting some Excel data in the right format.

I have a file that looks like this:

What I need to do is to create one row for each project and an estimate column and an actual column, so it looks like:

I cannot figure out how to make these kinds of transformations to merge two rows into one.

I have already loaded the Excel file into a QVD.

Thanks for any help

Mike

Need to combine rows in Excel files

prieper — Thu, 10 Mar 2011 23:11:53 GMT

Check the OL-Help for CROSSTABLE, thus your script may look like

CROSSTABLE(Month, Value, 3) LOAD * FROM YourExcel....;
DROP FIELD Total;

This should result in a normalized table like
Proj1 Estimate Jan 2011 12.9
Proj1 Actual Jan 2011 93.7
etc.

Here you may place a pivot-table over it and drag the column accordingly.
If you need to have exactly the header as described, you need to calculate and expression and concatenate the two field, like

LEFT(Type, 3) & Month AS NewHeader

HTH
Peter

Need to combine rows in Excel files

llauses243 — Fri, 11 Mar 2011 00:01:45 GMT

Hi Peter ,

Is this case about unnormalize, non normalization ?¡ Is correct my observación ?

Is correct your solution vía Chart/Pivot

Thx, Luis.

Need to combine rows in Excel files

rwunderlich — Fri, 11 Mar 2011 04:24:43 GMT

Do you have columns for each month or just one month? You may also want to employ GENERIC LOAD. See

http://qlikviewnotes.blogspot.com/2010/05/use-cases-for-generic-load.html

Need to combine rows in Excel files

Fri, 11 Mar 2011 15:47:09 GMT

Currently, I have data for three months, but it has the potential to get more.

I will look at the generic load to see if that will solve my problem.

Thanks