Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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!