QlikView: Modify Data in Column After in Load Script?

[kusterere]

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!

[sunny_talwar]

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

[martinpohl]

load your fy:

load

all other fields,

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

regards

[martinpohl]

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

[m_woolf]

You could use:

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

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

[Clever_Anjos · Accepted solution]

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?

[kusterere]

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

[kusterere]

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.

[kusterere]

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.