-
QlikView – extract data from comma separated cell
Posted on September 18th, 2008 3 commentsHello all who read this :-)
Today i have a collision with little odd situation. I have an excel file with 2 columns – ID and Users. The problem was that in the Users cells the data was comaseparated like this:
ID Users 1 User1,User2,User3 2 User4,User5,User6 3 User7,User8,User9 So the situation i want is to have table with only one use per row. Something like this:
ID Users 1 User1 1 User2 1 User3 2 User3 2 User4 2 User5 Below is a QlikView script that achieve this:
// // ++++++++++ Load data from excel ++++++++++for a=0 to 20 // // Presume that there will no be more than 20 users listed.// // For each a from 0 to 20 do load from excel where substring count , is equeal to aExcelData:LOAD
ID,
Users
$(a) AS a
FROM [C:\Users.xls] (biff, embedded labels, table is [Users$])
WHERE substringcount(Users, ',') = $(a);
// // For each a extract all listed users. The result is that for each listed customer for request
// // there is a single record
for b=0 to $(a)
UnQualify *;
TransforemdExcelData:
Load
ID,
if( substringcount(Users, ',') = 0, Users,
mid(Users&',', index(Users&',', ',',$(b))+1, index(Users&',', ',',$(b)+1) - index(Users&',', ',',$(b))-1)
) AS User,
a
Resident ExcelData;
next
next
// Finaly TransforemdExcelData may be loaded again and exclude nullable records
// // ++++++++++ END loading data from excel++++++++++
// // Load all data in new table excluding empty record
Qualify a;
Users:
Load
ID,
User,
a
Resident TransforemdExcelData
Where User<> '';
