topic Re: Convert raw to column in QlikView

Convert raw to column Generic Load

alec1982 — Mon, 27 Aug 2012 16:08:08 GMT

Hi,

I have at ble that shows data as folow:

ID CustomField Custom value

1 Space 500

1 Total Employees 20

1 TotalEmployee 5

1 Division ABC

2 Space 250

2 Total Employees 10

2 Division XYZ

Is there anyway I can make the custom Fieldas a column and then custom value is the value for it such as

ID Space totalEmployee Division

1 500 20 ABC

2 250 10 XYZ

Thanks for your help

Re: Convert raw to column

roger_stone — Mon, 27 Aug 2012 16:20:28 GMT

Have a look at the CROSSTABLE option for loading your file.

Re: Convert raw to column

whiteline — Mon, 27 Aug 2012 16:26:07 GMT

CrossTabel is not suitable here.

Try Generic Load instead (Chapter 24.1 in reference manual).

upd:

It creates one table for each CustomField with ID as a key value.

Re: Convert raw to column

pgalvezt — Mon, 27 Aug 2012 16:26:53 GMT

Load the base, Next, enable transformation step, Rotate,Option Transpose.

Re: Convert raw to column

Mon, 27 Aug 2012 17:05:23 GMT

You can try this in SQL and/or use the nested query with your existing data as a load script.

Re: Convert raw to column

alec1982 — Mon, 27 Aug 2012 18:04:45 GMT

Hi pgalvezt,

How can I do that, do you have any details?

Thanks for your help.

alec,

Re: Convert raw to column

pgalvezt — Mon, 27 Aug 2012 18:18:38 GMT

When you open a file excel for example, Qlikview will show you an assistance file, you have to do clic in next, after that a large buttom says enable transformation step, the last sheet says "rotate" there you can convert colummn in rows and do more actions too.

Re: Convert raw to column

alec1982 — Mon, 27 Aug 2012 18:49:07 GMT

Th problem is I am loading from a huge XML file and it is almost impossible to go over the tables using this method.

Thxs.

Re: Convert raw to column

alec1982 — Mon, 27 Aug 2012 19:00:43 GMT

Thank you guys for your help.

By following the generic load I ended up having the following script:

original:

LOAD RecordId As Field1,

CustomFieldLabel As Attribute,

CustomFieldValue as Value

FROM [..\data\harborflex\hbdata.xml] (XmlSimple, Table is [PortfolioExportData/tblCustomFieldValue]);

temp1:

generic load * resident original;

result:

load distinct Field1 resident original;

drop table original;

FOR i = 0 to NoOfTables()

TableList:

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

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

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

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

LEFT JOIN (result) LOAD * RESIDENT $(vTable);

DROP TABLE $(vTable);

NEXT i

drop table TableList;

I am still getting an error even using this clear script. the error is happening while doing the Join(Result)

Anybody can help.

Thanks,

Re: Convert raw to column

whiteline — Mon, 27 Aug 2012 19:10:30 GMT

alec1982, what kind of error ?

Re: Convert raw to column

Mon, 27 Aug 2012 19:28:48 GMT

Try

result:

NoConcatenate

load distinct Field1 resident original;

Re: Convert raw to column

alec1982 — Mon, 27 Aug 2012 20:01:33 GMT

Table not found

LEFT JOIN (result) LOAD * RESIDENT temp1.# of Break Rooms

Thanks,

Re: Convert raw to column

whiteline — Mon, 27 Aug 2012 20:08:42 GMT

Ok, it seems the problem is with spaces and #.

Try to replace symbol # with something else when load table "original".

Re: Convert raw to column

alec1982 — Mon, 27 Aug 2012 21:22:28 GMT

Hi,

thanks for the reply.

The load is coming from an XML file and # is a value in the second column.

how can I do that.

Thxs,

Re: Convert raw to column

whiteline — Tue, 28 Aug 2012 07:22:34 GMT

To replace all '#' within 'N' as an example try this:

original:

LOAD RecordId As Field1,

CustomFieldLabel As Attribute,

Replace(CustomFieldLabel, '#', 'N') As Attribute,

CustomFieldValue as Value

FROM [..\data\harborflex\hbdata.xml] (XmlSimple, Table is [PortfolioExportData/tblCustomFieldValue]);

You can also use purgechar function to skip some special symbols.

Re: Convert raw to column

whiteline — Tue, 28 Aug 2012 14:48:49 GMT

Found it. Your problem as I said was spaces.

Your previous code works perfect, you should just use [] with table name.

FOR i = 1 to FieldValueCount('Tablename')

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

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

DROP TABLE [$(vTable)];

NEXT i

Re: Convert raw to column

rwunderlich — Tue, 28 Aug 2012 17:22:09 GMT

Or use the Qlikview Components version of the above code:

CALL Qvc.JoinGenericTables('result', 'temp1');

-Rob