topic Re: Fetch values using Peek(). Getting <NULL> as return value in QlikView

Fetch values using Peek(). Getting as return value

Mon, 01 Jun 2015 08:47:10 GMT

Hi,

I have just started learning QlikView. I am trying to fetch the values from a column [Employee ID]. The data has already been loaded in QlikView from the excel. I need to split the ID after the '-' character and get the date component (8 characters after '-'). The ID format is T-18102015 (18-Day, 10-Month, 2015-Year). After this, the date format needs to be divided into separate columns : Day, Month & Year. Could someone help me with the code to do so?

There is a line that I wrote in order to fetch the column values.However, it returns <NULL> value in the variable when checked in the debugger. Could someone help me understand what is wrong with the script below -

EmpTable:

LOAD [Unique ID],

[First Name],

[Middle Name],

[Last Name],

[Employee Id],

[Last Name],

[Address],

[Birth Date],

[Total Experience]

FROM

[..EmployeeDetails.xlsx]

(ooxml, embedded labels, table is Emp);

Let nRows = NoOfRows('EmpTable');

For i = 0 To nRows

Let strEmpID = peek('[Employee ID]',$(i), 'EmpTable'); // Used to fetch values under [Employee ID] column. Returns <NULL>

Let empIDYear = Mid(strEmpID ,3); // Get the date component which starts from the 3rd character

Let lenEmpID= Len($(empIDYear )); // Get the length of the string

// Further logic needs to be developed

Next i

Any help on the above questions is highly appreciated. Also if somebody can suggest a reference to the tutorials for the solution to similar issues/concerns, that would be very helpful!

Thanks in advance!

Re: Fetch values using Peek(). Getting as return value

Colin-Albert — Mon, 01 Jun 2015 15:13:08 GMT

QlikView is case sensitive

Let strEmpID = peek('[Employee ID]',$(i), 'EmpTable');

should be

Let strEmpID = peek('[Employee Id]',$(i), 'EmpTable');

Re: Fetch values using Peek(). Getting as return value

swuehl — Mon, 01 Jun 2015 15:13:21 GMT

QV is case sensitive, your field name is [Employee Id]:

Let strEmpID = peek('[Employee Id]',$(i), 'EmpTable');

Re: Fetch values using Peek(). Getting as return value

maxgro — Mon, 01 Jun 2015 15:14:45 GMT

try without []

Let strEmpID = peek('Employee ID',$(i), 'EmpTable');

Re: Fetch values using Peek(). Getting as return value

alexandros17 — Mon, 01 Jun 2015 15:17:09 GMT

Do this:

EmpTable:

LOAD [Unique ID],

[First Name],

[Middle Name],

[Last Name],

[Employee Id],

[Last Name],

[Address],

[Birth Date],

[Total Experience]

FROM

[..EmployeeDetails.xlsx]

(ooxml, embedded labels, table is Emp);

MyTable:

noconcatenate

Load *, Mid( [Employee Id] ,3) as FullDate, Mid( [Employee Id] ,3,2) as day, Mid( [Employee Id] ,6,2) as month, Mid( [Employee Id] ,9) as year resident EmpTable;

drop table EmpTable;

Re: Fetch values using Peek(). Getting as return value

swuehl — Mon, 01 Jun 2015 15:26:40 GMT

In addition, I believe your loop should run until nRows-1

Re: Fetch values using Peek(). Getting as return value

Tue, 02 Jun 2015 04:13:30 GMT

Thank you everyone! Braces removal helped!

Re: Fetch values using Peek(). Getting as return value

Tue, 02 Jun 2015 05:49:09 GMT

Thanks Alessandro Saccone! Tried your solution, works perfectly!

My only question here would be regarding a field similar to Employee ID. Suppose I had a field which looks like CODE-112233445566 and needed categorization like 11, 22, 33, 44 and so on. In this example I have 6 categories. But every row may have different categories. Will a for loop be required in such case? How will the code look like?

Thanks in advance!

Re: Fetch values using Peek(). Getting as return value

Tue, 02 Jun 2015 05:50:15 GMT

Thanks Colin, point taken!