Manipulate a field in existing data

[#1]

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

[#2]

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

[#3]

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

[#4]

TU ME PUEDES AYUDAR HELP

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

DE BINARY A CARACTER

GRACIAS

[#5]

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

[#6 · Accepted solution]

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

[#7]

Hi,

answering your 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.

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;

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

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

[#8]

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

[#9]

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

[#10]

Hi Arif,

you're missing the alias. It must be:

LOAD *, Month(Run_Date) AS Run_Date_Month

FROM...

cheers

Florian