Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
daniel_berg
Contributor II
Contributor II

Crosstable?

Hi,

I have this space delimited file that look like this:

[1234] OrderId
[12]CustomerId

[20140101]      OrderDate                   

[CustomerA]     CustomerName     

[1235] OrderId
[13]CustomerId

[20140102]      OrderDate                   

[CustomerB]     CustomerName      

I want to use it like this:

OrderId     CustomerId     OrderDate     CustomerName

1234          12                    20140101       CustomerA

1235          13                    20140102       CustomerB

...              ...                    ...                  ...

..               ..                     ..                   ..

Can anyone help me?               

A simple txt file Message was edited by: Daniel Berg

1 Solution

Accepted Solutions
Henric_Cronström

TempData:

LOAD *,

  If(FieldName='OrderId',FieldValue,Peek(Key)) as Key;

LOAD

  Trim(PurgeChar(@1,'[]')) as FieldValue,

  Trim(@2) as FieldName

FROM cust.txt (txt, codepage is 1252, no labels, delimiter is ']', msq);

Data:

Generic

Load Key, FieldName, FieldValue Resident TempData;

Drop Table TempData;

HIC

View solution in original post

7 Replies
vikasmahajan

Please attach any file so that members can help you.

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
daniel_berg
Contributor II
Contributor II
Author

Does anyone know the answer to my question?

Henric_Cronström

TempData:

LOAD *,

  If(FieldName='OrderId',FieldValue,Peek(Key)) as Key;

LOAD

  Trim(PurgeChar(@1,'[]')) as FieldValue,

  Trim(@2) as FieldName

FROM cust.txt (txt, codepage is 1252, no labels, delimiter is ']', msq);

Data:

Generic

Load Key, FieldName, FieldValue Resident TempData;

Drop Table TempData;

HIC

sujeetsingh
Master III
Master III

Danial go for Generic Load

sujeetsingh
Master III
Master III

Here is a Sample code just copy it and create a sample for you

Table:

LOAD * INLINE [

    ID, Name, Type,Value

    1, A, X,1

    2, A, Y,1

    3, A, Z,1

    4, A, X,1

];

TableS:

Generic LOAD * Resident Table;

FOR i = 0 to NoOfTables()

  TableList:

  LOAD TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'TableS.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (Table) LOAD * RESIDENT [$(vTable)];

  DROP TABLE $(vTable);

NEXT i

DROP TABLE TableList;

Not applicable

Daniel

You can Pull This table with Spaces Delimited instead of Space Delimited.

You can find the Attached File  for your Solution.

Thanks and Regards

daniel_berg
Contributor II
Contributor II
Author

Works like a charm. Thank You!