topic Re: QlikView: Modify Data in Column After in Load Script? in QlikView

QlikView: Modify Data in Column After in Load Script?

kusterere — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I am not sure if this is something that is possible or not. Here is what is happening. I have several different departments that track their data on Excel. Easy right? Most of the time yes. However, I am kind of nit picky about how I present my reports. While some of the departments use "FY ####" others use "FY##" in their fiscal year column.

So is there a way to fix this this in the load script? If I wanted to have the data for [Effective FY] changed to "FY 2018" when it says "FY18" can I do that?

I am super bad at coding in QlikView so any help would be appreciated.

Thanks so much!

Re: QlikView: Modify Data in Column After in Load Script?

sunny_talwar — Fri, 15 Dec 2017 17:28:13 GMT

May be this

If(Len([Effective FY]) = 4, 'FY ' & Year(Date#(Right([Effective FY], 2), 'YY')), [Effective FY]) as [Effective FY]

So basically check if the lenght of Effective FY is 4 or not, if it is 4, then manipulate it to look like the other year value

Re: QlikView: Modify Data in Column After in Load Script?

martinpohl — Fri, 15 Dec 2017 17:28:40 GMT

load your fy:

load

all other fields,

if(len([Effective FY]=5,'FY '&2000+right([Effective FY],[Effective FY]) as [Effective FY]

regards

Re: QlikView: Modify Data in Column After in Load Script?

martinpohl — Fri, 15 Dec 2017 17:29:37 GMT

len 4 (without blank) or 5 (with blank) in field

Re: QlikView: Modify Data in Column After in Load Script?

m_woolf — Fri, 15 Dec 2017 17:30:03 GMT

You could use:

'FY '20' & right(FiscalYear,2) as FY,

That would break in 2100, but you probably wouldn't care by then.

Re: QlikView: Modify Data in Column After in Load Script?

Clever_Anjos — Fri, 15 Dec 2017 17:36:07 GMT

Use this before any loads

Map:

mapping load * inline [

F1, F2

FY18, FY2018

FY 18, FY2018

];

Map [Effective FY] using Map; // is [Effective FY] your fieldname?

Re: QlikView: Modify Data in Column After in Load Script?

kusterere — Wed, 10 Jan 2018 16:15:13 GMT

I really wouldn't. If I did then I have bigger problems. LOL

Re: QlikView: Modify Data in Column After in Load Script?

kusterere — Wed, 10 Jan 2018 16:16:47 GMT

I would be changing all of the imported data to [Fiscal Year] actually. Effective FY is one of the fun column names that someone in the departments uses since they data sometimes goes over multiple fiscal years.

Re: QlikView: Modify Data in Column After in Load Script?

kusterere — Wed, 14 Mar 2018 17:36:41 GMT

I never could get this to work properly with my table but I was able to load a new table that let me use link the undesired format to the desired format.

//Load Data Table

OrgChange1:

LOAD

{removed other fields to make this shorter}

[FY Complete Date] as FYCompleteDate,

FROM

$(vDataSave)Tracking Worksheet.xls

(biff, embedded labels, table is [Qlikview Compatible$]);

//Load FY Year Table:

FYYearTable:

Load *

Inline [

FYCompleteDate, FYNumLong

FY13, FY 2013

FY14, FY 2014

FY15, FY 2015

FY16, FY 2016

FY17, FY 2017

FY18, FY 2018

] ;

It is not as clean as mapping but it worked for me because I was able to get the charts to grab the FYNumLong field instead of FYCompleteDate which made everything look the same.