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

[#2]

Try

result:

NoConcatenate

load distinct Field1 resident original;

[alec1982]

Table not found

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

Thanks,

[whiteline]

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

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

[alec1982]

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,

[whiteline]

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.

[whiteline]

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

[rwunderlich]

Or use the Qlikview Components version of the above code:

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

-Rob