Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
kusterere
Contributor
Contributor

QlikView: Modify Data in Column After in Load Script?

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!

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

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?

View solution in original post

8 Replies
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
Partner
Partner

load your fy:

load

all other fields,

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

regards

martinpohl
Partner
Partner

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

m_woolf
Master II
Master II

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
Employee
Employee

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?

View solution in original post

kusterere
Contributor
Contributor

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

kusterere
Contributor
Contributor

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
Contributor
Contributor

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.