topic Re: how to format in QlikView

how to format

Wed, 17 Sep 2014 20:32:46 GMT

Hi Everyone

I have data in text file in following format in straight line one after another.

Administrators

user1

user2

user4

user5

Remote Desktop Users

user5

user8

user3

how can i change via QlikView ETL so it becomes in following format.

Administrator Remote Desktop Users

user1 user5

user2 user8

user4 user3

user5

Re: how to format

MarcoWedel — Wed, 17 Sep 2014 23:23:45 GMT

Hi,

one possible solution:

tabTextFile:
LOAD *,
    If(Match(Line, 'Administrators', 'Remote Desktop Users'), Line, Peek(Type)) as Type
Inline [
Line
Administrators
    user1
    user2
    user4
    user5


Remote Desktop Users
    user5
    user8
    user3
];

tabOutput:
Generic LOAD
  AutoNumber(RecNo(), Type) as ID,
  Type,
  Line
Resident tabTextFile
Where Type<>Line;

hope this helps

regards

Marco

Re: how to format

nagaiank — Thu, 18 Sep 2014 00:53:55 GMT

The following script also works:

LOAD RowNo() as ID, * Inline [

Admin

user1

user2

user3

user4

];

LOAD RowNo() as ID, * Inline [

User

user5

user6

user7

];

left Join (A) LOAD * Resident B;

DROP Table B;