topic Re: Load and rename field in QlikView

Load and rename field

Tue, 04 Nov 2014 08:48:35 GMT

Hi all!

I want first tothank you all for your support and continue by asking a question.

I want to load data and rename the field in the scrript it should look like this:

TMP:

LOAD

RIGHT(TRIM(@1),4) AS Jaar

FROM $(vG.BasePath)$(vL.BaseImport)SSC_Jaarplan_$(v.HuidigSeizoen).xlsx

(ooxml, no labels, table is [Uploadsheet jaarplan], filters(

Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))

));

In the name of the table, the 4 last caracters are 2015 and I want 2015 to renamed as year (jaar) in dutch language.

anyone who can help please.

Re: Load and rename field

ashfaq_haseeb — Tue, 04 Nov 2014 08:57:46 GMT

Hi,

Try this

LOAD

RIGHT(TRIM(@1),4) AS Jaar,

right(Filebasename(),4) as Year,

FROM $(vG.BasePath)$(vL.BaseImport)SSC_Jaarplan_$(v.HuidigSeizoen).xlsx

(ooxml, no labels, table is [Uploadsheet jaarplan], filters(

Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))

));

Regards

ASHFAQ

Re: Load and rename field

jagan — Tue, 04 Nov 2014 09:04:09 GMT

Hi,

Try FileBaseName() to get the loading file name and then use Right() to get right most characters

LOAD

Right(TRIM(@1),4) AS Jaar,

Right(Filebasename(),4) as Year

FROM $(vG.BasePath)$(vL.BaseImport)SSC_Jaarplan_$(v.HuidigSeizoen).xlsx

(ooxml, no labels, table is [Uploadsheet jaarplan], filters(

Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))

));

Regards,

jagan.

Re: Load and rename field

its_anandrjs — Tue, 04 Nov 2014 09:26:28 GMT

Hi,

This is your field expression RIGHT(TRIM(@1),4) AS Jaar

Or you want to extract from the Filename the Year values. Or you can try the below expression also

Load

Year(Date(Date#( RIGHT(TRIM(@1),4),'YYYY'),'YYYY') ) AS Jaar,

Right(Filebasename(),4) as NewTestField

FROM $(vG.BasePath)$(vL.BaseImport)SSC_Jaarplan_$(v.HuidigSeizoen).xlsx

(ooxml, no labels, table is [Uploadsheet jaarplan], filters(

Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Bottom, 1), Select(1, 0)))

));

Regards

Anand

Re: Load and rename field

Tue, 04 Nov 2014 09:33:38 GMT

Actually what I don't know is how step by step I can load the file i have and use filters and renaming. after choosing table files and enable transformation step, I can conditionaly delete some rows but I'm failing to rename the Right(TRIM(@1),4) AS Jaar

Re: Load and rename field

its_anandrjs — Tue, 04 Nov 2014 09:37:24 GMT

What comes in the field @1 and i believe by using Right(TRIM(@1),4) AS Jaar you rename @1 field to Jaar will it works or not nut your expression is correct.

Regards

Anand

Re: Load and rename field

ashfaq_haseeb — Tue, 04 Nov 2014 10:01:01 GMT

Hi,

Can you post sample data

Regards

ASHFAQ

Re: Load and rename field

MarcoWedel — Tue, 04 Nov 2014 10:13:23 GMT

$(v.HuidigSeizoen) as year