Convert raw to column Generic Load

[alec1982]

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

[roger_stone]

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

[whiteline]

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.

[pgalvezt]

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

[#5]

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

[alec1982]

Hi pgalvezt,

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

Thanks for your help.

alec,

[pgalvezt]

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.

[alec1982]

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.

[alec1982]

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,

[whiteline]

alec1982, what kind of error ?