topic Re: Help required to create mapping load statement based on certain condition in QlikView

Help required to create mapping load statement based on certain condition

qlikviewforum — Tue, 28 Apr 2015 12:03:26 GMT

We have EmployeeID, Date and Salary in a table(It contains yearly data). What we need is, we want to know what is the current salary(Based on Max(Date)) against each EmployeeID. Since we need to use this as an mapping load so that we can get the current salary of an employee against the EmployeeID(Using the applymap).

Please help

Re: Help required to create mapping load statement based on certain condition

buzzy996 — Tue, 28 Apr 2015 12:05:34 GMT

look at reference manual for applymap() syntax.

Re: Help required to create mapping load statement based on certain condition

qlikviewforum — Tue, 28 Apr 2015 12:08:20 GMT

Thanks for your response. I want to create a mapping load which contains current salary against employeeid so that we can use applymap and get required information.

Re: Help required to create mapping load statement based on certain condition

Anonymous — Tue, 28 Apr 2015 12:14:50 GMT

Try this:

source:
LOAD
EmployeeID,
Date,
Salary
From someplace;

INNER JOIN (source) LOAD
EmployeeID,
max(Date) as Date
RESIDENT source
GROUP BY EmployeeID;

EmployeeSalaryMap:
MAPPING LOAD DISTINCT
EmployeeID,
Salary
FROM source;

Re: Help required to create mapping load statement based on certain condition

Gabriel — Tue, 28 Apr 2015 12:36:24 GMT

Hi,

What I would try is have 1 table with the Max Date ORDER BY Date DESC, then Peek() the Date from that table and have MAPPING LOAD table for EMPID & Salary using WHERE clause for Date = WHateveer the name of your Peek above is. Finally use you Applymap() function.

i.e.

MaxDate:

LOAD

Date

FROM .......EmployeeTable

ORDER BY Date DESC;

LET vMaxDate = PEEK('Date');

Drop Table MaxDate;

//Mapping table

MAPPING LOAD

EmpID

,Salary

FROM.......EmployeeTable

WHERE Date = $(vMaxDate);

Re: Help required to create mapping load statement based on certain condition

Anonymous — Tue, 28 Apr 2015 13:16:50 GMT

Hi,

find attached app ,

you ma need to modify according your need.

Thanks

BKC