Crosstable?

[daniel_berg]

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

[vikasmahajan]

Please attach any file so that members can help you.

Vikas

[daniel_berg]

Does anyone know the answer to my question?

[hic · Accepted solution]

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]

Danial go for Generic Load

[sujeetsingh]

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;

[#7]

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]

Works like a charm. Thank You!