topic Re: Crosstable? in QlikView

Crosstable?

daniel_berg — Mon, 24 Feb 2014 08:32:29 GMT

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

Re: Crosstable?

vikasmahajan — Mon, 24 Feb 2014 09:16:18 GMT

Please attach any file so that members can help you.

Vikas

Re: Crosstable?

daniel_berg — Tue, 25 Feb 2014 06:35:28 GMT

Does anyone know the answer to my question?

Re: Crosstable?

hic — Tue, 25 Feb 2014 06:59:42 GMT

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

Re: Crosstable?

sujeetsingh — Tue, 25 Feb 2014 07:01:22 GMT

Danial go for Generic Load

Re: Crosstable?

sujeetsingh — Tue, 25 Feb 2014 07:03:01 GMT

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;

Re: Crosstable?

Tue, 25 Feb 2014 07:19:32 GMT

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

Re: Crosstable?

daniel_berg — Tue, 25 Feb 2014 07:36:59 GMT

Works like a charm. Thank You!