Spliting Corresponding Row data into columns.

[Analyst240]

Hello,

The attached image represents a custom field and a associated ID. The way the fields are imported in I can only seem to show them as is. What I am hoping for is instead of having 2 rows per ID I would like to shift both values for each ID into two different columns. For example: TP:129326 shows twice. I want to show RequestSLA and its value in Column B then then corresponding Response SLA value would be shown in column C.  I have been looking at this for sometime now and am at a loss on how this would be done.

Any advice is helpful. I am new with Qlik as of the past two or so months. 

Thanks,

[Tim_Wensink]

Hi,

you could try this code (I tested it using an Excel file, could be that you need to change the From part to suit your

circumstances):

//First, load only the CustomField values starting with 'Request SLA'
Test:
LOAD TP,
CustomField as RequestSLA
FROM
test.xlsx
(ooxml, embedded labels, table is Blad1)
where wildmatch(CustomField,'Request SLA*');

join(Test)

//Then, join the CustomField values starting with 'Request SLA'
LOAD TP,
CustomField as ResponseSLA
FROM
test.xlsx
(ooxml, embedded labels, table is Blad1)
where wildmatch(CustomField,'Response SLA*');

This creates a table with 3 fields: TP, RequestSLA and ResponseSLA.

The text 'RequestSLA' and 'ResponseSLA' are still present in your text field, if you don't want this 

you could try 'right(CustomField,5) as ResponseSLA'  to only grab the 5 characters on the right of your field.

Regards,

Tim Wensink

[Analyst240]

Thanks! This is very helpful! So I should have said something but forgot. My custom column is built from two other columns. CustomFieldData and CustomFieldName. Here is the code I put together with what you gave me. From the query I see already built here I don't have any from. So I continued the pattern of using Resident. I'm not sure if that's similar or not. I learned some syntax though and this seems to be a good path. My issue now is I am seeing a circular reference being made. I sort of understand what its talking about still am not sure about what I should remove to fix this.(See attached image)

[TESTTABLE]:
LOAD [Id_u4] AS [TP],
[Name_u0] AS [CustomFieldName],
[@Content] AS [CustomFieldData]
Resident RestConnectorMasterTable
where wildmatch([Name_u0],'Request SLA*');

Join

LOAD [Id_u4] AS [TP],
[Name_u0] AS [CustomFieldName],
[@Content] AS [CustomFieldData]
Resident RestConnectorMasterTable
Where wildmatch([Name_u0],'Response SLA*');

[Tim_Wensink]

Hi,

A resident load means you are loading data from a table that is already loaded in memory, whereas a from means you are loading data from another source (database, excel file etc.). 

The circular reference is caused by the fact that there is e _KEY_field in table SLA TEST which 'points' to the same field in table Value, table Value has a field CustomFieldData which points to the same field in table TESTTABLE, and table TESTTABLE has a field CustomFieldName which then points to table SLA TEST. 

I am not quite sure in what table you want the field CustomFieldName to end up in, but the solution for this is ususally dropping the field from one or more tables or dropping entire tables (since you have 2 tables containing TEST in its name, I suggest dropping one of those tables at the end of your script.

Regards,

Tim Wensink