topic AW:Manipulate a field in existing data in QlikView

Manipulate a field in existing data

Tue, 22 Feb 2011 15:41:11 GMT

Hello,

I have loaded some data into qlikview. There is one field called run_date. I want to add one more column called my_month into the Qlikview table. This would be calculated as Month(Run_Date) as my_month.

Now my question is how do i perform this task using load statement. will the data be loaded again from database? . Do i have to include the rest of columns too in the load statement, or do i just need to include the renaming column, and i dont need to disturb the rest of data. I am new to Qlikview so kindly elaborate it simply

-br

Syed

AW:Manipulate a field in existing data

Tue, 22 Feb 2011 15:44:16 GMT

Hi ArifShah,

as simple as:

LOAD Run_Date,

Month(Run_Date) AS RunDateMonth

FROM ...

This one is calculated during the load script execution and than hardlinked in your data. If you don't need the Run_Date itself, simply take it off your LOAD (and SQL) statement.

cheers

Florian

Manipulate a field in existing data

Tue, 22 Feb 2011 15:46:07 GMT

AMIGO PUEDES CREAR UNA NUEVA COLUMNA ALTER_COLUMN

TU SABES COMO PUEDO CAMBIAR EL TIPO DE DATO DE BINARY A CARATER DESDE LA CARGA TE LO AGRADEZCO

AW:Manipulate a field in existing data

Tue, 22 Feb 2011 15:47:49 GMT

TU ME PUEDES AYUDAR HELP

COMO CAMBIO UN TIPO DE DATO EN UN CAMPO DESDE LA CARGA

DE BINARY A CARACTER

GRACIAS

AW:Manipulate a field in existing data

Tue, 22 Feb 2011 16:13:51 GMT

Thanks,

That helped. I actually have two questions

1) If the data has already been loaded, and i just need to add this month field, what would i write in the from field. If i just add Month(run_date) as my_month in the load statement, will it remove the rest of columns or they will be there.

2) If i include all the fields in the select statement, then can i just include one field in the load statement where i want to manipulate only one field, or do i still have to include all the fields from the select field, even if the manipulation has to be on a single field. I will need all the fields in my charts though

-br

Syed

AW:Re: AW:Manipulate a field in existing data

Tue, 22 Feb 2011 16:18:04 GMT

Hi Syed,

of course you need to have all fields in your LOAD statement which you may use later on. To simplify that, you can use

LOAD *,

Month(Run_Date) AS Run_Date_Month

FROM ...

If data was loaded before you can use the Month() function in your calculations, but you should avoid such live calculations and prefer to put as many of them as possible into your load script.

cheers

Florian

AW:Manipulate a field in existing data

Tue, 22 Feb 2011 16:58:26 GMT

Hi,

answering your questions:

If you already load the table and want to add the my_month field in another load statement you can use join:

test:
LOAD field1,
field2,
field3,
Run_Date
FROM table1;
left join
load Run_Date,
month(Run_Date) as my_month
resident test;

If you want all the fields and manipulate just one, Florian already gave you the answer:

LOAD *,
Month(Run_Date) AS Run_Date_Month
FROM ...

Hope this helps!

Regards

AW:Manipulate a field in existing data

Wed, 23 Feb 2011 09:04:00 GMT

Great. Its clear now. Thanks Florian, Thanks Gabriela. That helped a lot

AW:Manipulate a field in existing data

Wed, 23 Feb 2011 10:33:26 GMT

Hello,

I have another problem. My Project file name is KP.qvd. There is only one table inside my QV report and its name is V_QV_Database_Size. This table has around five fields. I want to add one field Month as i mentioned above. However, When I try the below code

Load *, Month(Run_Date)

from

(KP.qvw)[table is V_QV_Database_Size]

it gives an error message that field <Run_Date> does not exist. When i just use the below code

Load *

from

[KP.qvw](table is V_QV_Database_Size), it loads only a single field with grabage data inside it. The report that i currently have has correct data and is working quite fine. I have checked inside the table viewer and everything is there. All fields are there in this table. Any idea where am I making the mistake?

Arif

AW:Re: AW:Manipulate a field in existing data

Wed, 23 Feb 2011 10:40:29 GMT

Hi Arif,

you're missing the alias. It must be:

LOAD *, Month(Run_Date) AS Run_Date_Month

FROM...

cheers

Florian

AW:Re: AW:Manipulate a field in existing data

Wed, 23 Feb 2011 10:48:50 GMT

Yes, but even that does not work. I get the error that is attached in the image. It still says <Run_Date> does not exist

Arif

AW:Re: AW:Manipulate a field in existing data

Wed, 23 Feb 2011 10:51:32 GMT

As you can see in the attached image, all the fields exist there

Arif

AW:Re: AW:Re: AW:Manipulate a field in existing data

Wed, 23 Feb 2011 11:01:51 GMT

Hi Arif,

that is weird. Would you mind to post your QVD file?

cheers

Florian

AW:Re: AW:Re: AW:Manipulate a field in existing data

Wed, 23 Feb 2011 11:22:41 GMT

Sure,

I have attached the qvw file. Kindly check it. Also if you go to slider, and see the sort tab, the expression is disabled. Could you please see why can't i sort the slider by expression

Thanks

Arif

file:///C:/Documents%20and%20Settings/uarif/Desktop/KP_Test.qvw

AW:Re: AW:Re: AW:Manipulate a field in existing data

Wed, 23 Feb 2011 11:23:19 GMT

Ohh sorry, how do i post a file here?

AW:Re: AW:Re: AW:Re: AW:Manipulate a field in existing data

Wed, 23 Feb 2011 11:30:00 GMT

HI Arif,

use the reply option (not fast reply!) and go to tab "Options". There is a dialog to upload a file.

cheers

Florian

AW:Re: AW:Re: AW:Manipulate a field in existing data

Wed, 23 Feb 2011 11:34:27 GMT

Okay thanks

Here you go

Arif

AW:Re: AW:Re: AW:Re: AW:Manipulate a field in existing data

Wed, 23 Feb 2011 13:42:00 GMT

Hi Arif,

I can't work with that one, as I don't have the ODBC connection to your source. I thought about the QVD file spoke about earlier.

cheers

Florian

AW:Re: AW:Re: AW:Re: AW:Manipulate a field in existing data

Wed, 23 Feb 2011 13:47:19 GMT

I thought it will have data when you load it into your QV because i had loaded the data into it. does it generate a seperate file for data? i can only see one file there which i uploaded. i thought it stores the data in the same file.

I used odbc to load the data first time, but i thought you wont need the odbc connection again. I thought we can just write a load script there to play with the data that is already in the file. Am i missing something?

and thanks a lot for giving me soo much help

Arif

AW:Re: AW:Re: AW:Re: AW:Re: AW:Manipulate a field in existing data

Wed, 23 Feb 2011 13:57:54 GMT

Hi Arif,

no, if you want to test a load script, you need a data connection which won't be working for me in this case. You can't do a regular load from a QVW file, only from QVDs.

Anyway I checked the field name in your script and saw it is all lower case. Although it is a Win system, QlikView works case sensitivie, so maybe you need to type "run_date" instead of "Run_Date".

cheers

Florian