how to format text file in following manner

[#1]

i have text file that gives me windows group name and its member in following format.

Administrators

  user1

  user2

  user3

Backup Operators

Performance_group

  user1

  user5

  user7

Guests

How can i format it in qlikview in following way where groups are in one column and all the members of that group are in seperate column with group name beside it. example below.

Administrators     user1

Administrators     user2

Administrators     user3

Backup Operators

Performance_group     user1

Performance_group     user5

Performance_group     user7

Guest

[MarcoWedel]

How do you intendto decide which text is a group Name?

[Anonymous]

Assuming the first character of the user is always a space, then how about something like this ?

Set Verbatim = 1;

Temp :

load

  If(RowType='Group', User, Peek(Group)) as Group,

  *

;

load

  if( left ( User , 1 ) = ' ' , 'User' , 'Group' ) as RowType,

  *

;

LOAD * INLINE [

    User

    "Administrators"

    "  user1"

    "  user2"

    "  user3"

    "Backup Operators"

    "Performance_group"

    "  user1"

    "  user5"

    "  user7"

    "Guests"

];

Data :

load

  Group

resident Temp

;

outer Join  (Data)

load

  Group ,

  trim(User) as User

resident Temp

where not Group = User

;

drop table Temp ;

[MarcoWedel]

Hi,

yes, I didn't notice the indention.

How about:?

SET Verbatim = 1;

tabTxtFile:
LOAD If(Len(LTrim(txt))-Len(txt),LTrim(txt)) as member,
    If(Len(LTrim(txt))-Len(txt),Peek(group),txt) as group
Inline [
txt
Administrators
" user1"
" user2"
" user3"
Backup Operators
Performance_group
" user1"
" user5"
" user7"
Guests
];

hope this helps

regards

Marco

[MarcoWedel]

Please close thread if your question is answered

thanks

regards

Marco