topic Re: Loading Dynamic Column Name in QlikView

Loading Dynamic Column Name

jyothish8807 — Tue, 02 Dec 2014 05:57:28 GMT

Hello All,

I am facing an issue in loading an excel sheet which have a dynamic Column. It changes when ever a new data is entered in the sheet or to that particular column.

eg:

| ID | Name | Count=0 |

Over here the column "Count=0" the 0 will increase as we add data into the sheet. So for every time the name will change so how can I load this column? 0 is nothing but sum(entire row).

Regards

Re: Loading Dynamic Column Name

kushalthakral — Tue, 02 Dec 2014 06:14:55 GMT

Dear Jyothish

Can you please give an example for better understanding like a table and its content

Regards

Kushal Thakral

Re: Loading Dynamic Column Name

jyothish8807 — Tue, 02 Dec 2014 06:30:54 GMT

Hi All,

PFA Spreadsheet. In this sheet when ever you enter a number in Count Column the count changes.

Regards

Re: Loading Dynamic Column Name

rustyfishbones — Tue, 02 Dec 2014 06:54:14 GMT

I am not sure you can dynamically change the field

Re: Loading Dynamic Column Name

kushalthakral — Tue, 02 Dec 2014 06:57:35 GMT

Hi Jyothish

Why dont you try the header in Qlikview GUI, at script level it is not possible but at GUI level you can create it.

Regards

kushal

Re: Loading Dynamic Column Name

jyothish8807 — Tue, 02 Dec 2014 07:46:05 GMT

But I am not able load that that field into qlikview, every time when I reload, the field name changes because someone from user side is updating the sheet.

Any solution for this will be really helpful.

Regards

Re: Loading Dynamic Column Name

rustyfishbones — Tue, 02 Dec 2014 07:49:45 GMT

You will need to change the column name in excel to something generic

and create the expression in Qlikview

You cant have a changing field name being imported as the script will fail.

Call this field something generic in Excel like Count and don't change it

Regards

Alan

Re: Loading Dynamic Column Name

jyothish8807 — Tue, 02 Dec 2014 07:53:25 GMT

Hmm now its tough since there are more than 100 sheets and all are being used. I cant go back to 100 sheets and make changes. Any way thanks a lot for the help.

Regards

Re: Loading Dynamic Column Name

r_wroblewski — Tue, 02 Dec 2014 07:57:43 GMT

Hi Jyothish

what you can do is to ignore the header for the excel files for load script.

Regards,

Ronny

Re: Loading Dynamic Column Name

anbu1984 — Tue, 02 Dec 2014 07:58:34 GMT

LOAD A As ID,

B As Name,

C As Count

FROM

(ooxml, explicit labels, header is 1 lines, table is Sheet1);

Re: Loading Dynamic Column Name

jyothish8807 — Tue, 02 Dec 2014 08:01:43 GMT

Hi Ronny,

But that's an important field and I want that in my QV.

Regards

Re: Loading Dynamic Column Name

r_wroblewski — Tue, 02 Dec 2014 08:04:32 GMT

But you can use set analysis to get this field value inside your application.

Just to understand you right you want to keep Count= Sum(column). as a field value right ?

The problem is you need a static column and when you need the value as graph header etc. or textbox you can use set analysis

e.g. ='Count= ' and Sum(Count)

Re: Loading Dynamic Column Name

jyothish8807 — Tue, 02 Dec 2014 08:07:48 GMT

If I ignore or comment the field in script then how can I use it in Set analysis?

Regards

Re: Loading Dynamic Column Name

jyothish8807 — Tue, 02 Dec 2014 08:13:16 GMT

Hi Anbu,

I guess it will not work. Can you please use my workbook attached and try to reload it after updating the count field in workbook? The Column count changes every time once you update anything in workbook.

Any solution will be really helpful.

Regards

Re: Loading Dynamic Column Name

anbu1984 — Tue, 02 Dec 2014 08:16:48 GMT

I used your input file. It works for me after updating count in one of the rows. Can you check again.

Re: Loading Dynamic Column Name

r_wroblewski — Tue, 02 Dec 2014 08:17:43 GMT

It's working fine.

I also used it.

Please see attached qvw as example.

Re: Loading Dynamic Column Name

r_wroblewski — Tue, 02 Dec 2014 08:27:42 GMT

just see my example below.

as Header e.g. ='Column= ' & Sum(ColumnName)

or ='Column= ' & Sum({1} ColumnName) when you want it static with max.

Re: Loading Dynamic Column Name

jyothish8807 — Tue, 02 Dec 2014 08:49:53 GMT

But my fields are ID ,Name, Count=4. so when I load it I will get these field in qv but cant see these in your app?

Regards

Re: Loading Dynamic Column Name

Peter_Cammaert — Tue, 02 Dec 2014 08:51:53 GMT

It works perfectly.

Anbu's script is ignoring the column labels (header is 1 lines) and referring directly to the column names (A, B, C, ...). If you do not need the actual count value, this is the simplest approach.

The script assumes that the layout of all workbooks is the same. Therefor you'll need to rename the column identifiers back into the field names only once.

Re: Loading Dynamic Column Name

jyothish8807 — Tue, 02 Dec 2014 08:59:00 GMT

Got it, sorry I missed the part where you removed the headers. Thanks a lot for helping out

Regards