topic Re: How fill some cells from excel file? in QlikView

How fill some cells from excel file?

Mon, 30 Jun 2014 11:50:38 GMT

Hi all.

I have a Excel file with grouped data rows. Example:

Article	Nomenclature	Quantity
	Warehouse 1
	Inflow from 01-01-2014
4555-16	Mug	50
1924-16	Coffee Pot	20
10688-18	Teapot	10
	Outflow from 31-01-2014
455-16	Mug	15
1924-16	Coffee Pot	9
10688-18	Teapot	6

Yes! Stupid base

I need transpose and fill data and create next table in QV:

Warehouses	Inflow/Outflow	Article	Nomenclature	Quantity
Warehouse 1	Inflow from 01-01-2014	4555-16	Mug	50
Warehouse 1	Inflow from 01-01-2014	1924-16	Coffee Pot	20
Warehouse 1	Inflow from 01-01-2014	10688-18	Teapot	10
Warehouse 1	Outflow from 31-01-2014	455-16	Mug	15
Warehouse 1	Outflow from 31-01-2014	1924-16	Coffee Pot	9
Warehouse 1	Outflow from 31-01-2014	10688-18	Teapot	6

How I can did it? Any ideas.

Thank you.

Re: How fill some cells from excel file?

igor-st80 — Mon, 30 Jun 2014 12:08:02 GMT

macro in excel and run it before load data in QV

Re: How fill some cells from excel file?

Mon, 30 Jun 2014 13:10:14 GMT

If I understand correctly:

1. Open XLS file

2. Run Macros

3. Save XLS file after macros finished

4. Load in QV.

Correctly?

Re: How fill some cells from excel file?

rwunderlich — Mon, 30 Jun 2014 13:18:07 GMT

You can load this with some conditional logic and multiple passes over the data. See an example at:

Qlikview Cookbook: Load Input File Containing Multiple Record Types http://qlikviewcookbook.com/recipes/download-info/load-input-file-containing-multiple-record-types/

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: How fill some cells from excel file?

mahesh_agrawal — Mon, 30 Jun 2014 14:33:49 GMT

Hi Dmitry,

Please find the attached Excel along with QVW fille.

I hope it answers your problem.

Re: How fill some cells from excel file?

Mon, 30 Jun 2014 16:05:24 GMT

An easy and well performing solution.... Well Done Mahesh Agrawal

Re: How fill some cells from excel file?

preminqlik — Mon, 30 Jun 2014 16:14:53 GMT

hi mahesh..superlike

Re: How fill some cells from excel file?

Tue, 01 Jul 2014 07:12:43 GMT

Mahesh, it's great!

I created macro in excel with this principle, but I don't know how write it in QV.

Thank for solution and algorithm.

Re: How fill some cells from excel file?

mahesh_agrawal — Tue, 01 Jul 2014 07:19:11 GMT

You are most welcome

Re: How fill some cells from excel file?

rohit214 — Tue, 01 Jul 2014 08:49:13 GMT

well done mahesh