Skip to main content
Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Fetch values using Peek(). Getting <NULL> as return value

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!

8 Replies
Colin-Albert

QlikView is case sensitive

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

should be

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

swuehl
MVP
MVP

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

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

maxgro
MVP
MVP

try without []

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

alexandros17
Partner - Champion III
Partner - Champion III

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;

swuehl
MVP
MVP

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

Not applicable
Author

Thank you everyone! Braces removal helped!

Not applicable
Author

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!

Not applicable
Author

Thanks Colin, point taken!