Get field values between a set of values as separate fields.

[Anonymous]

Hi All,

I have a table as follows.

Available:

Field Name

Start Schedule A

Start Task A

Start Job A

Start File A

Message A1

Message A2

End File A

End Job A

End Task A

End Schdule A

Start Schedule B

Start Task B

Start Job B

Start File B

Message B1

Message B2

End File B

End Job B

End Task B

End Schdule B

The requirement is to read the values between "Start" and "End" in iterations and Message A1, Message A2 should be associated with TaskA. Message B1, Message B2 should be associated to Task B. Schedule, Task, Job and File should be the field names with the relevant values. The pictorial representation of fields, their values and the associations for the above example is as follows.

Schedule Name	Task Name	Job Name	File Name	Message
Schedule A	Task A	Job A	File A	Message A1
Schedule A	Task A	Job A	File A	Message A2
Schedule B	Task B	Job B	File B	Message B1
Schedule B	Task B	Job B	File B	Message B2

Kindly provide me some solution. I have been stuck up in finding a logic to achieve this format. Please let me know if any further information is required.

[sunny_talwar]

The data in Field Name will be formatted in the order that you have shown above?

[Anonymous]

Yes Sunny. When I load the table, that is how it looks.

[MarcoWedel]

Hi,

one solution might be:

table1:
LOAD *,
    RangeSum(Peek(Depth),DepthDelta) as Depth,
    If(DepthDelta,Peek(ID),RangeSum(Peek(ID),1)) as ID;
LOAD RecNo() as RecNo,
    [Field Name],
    Pick(WildMatch([Field Name],'Start*','End*','*'),1,-1,0) as DepthDelta
FROM [https://community.qlik.com/thread/280301] (html, codepage is 1252, embedded labels, table is @1);

table2:
LOAD *,
    Left(Peek(Path),Index(Peek(Path)&'/','/',Depth-RangeMax(0,DepthDelta))-1)&If(DepthDelta>=0,If(Depth>1,'/')&Replace([Field Name],'Start ','')) as Path
Resident table1;

DROP Table table1;

tabTemp:
Generic
LOAD ID,
    SubField(SubPath,' ',1)&' Name' as FieldName,
    SubPath;
LOAD ID,   
    SubField(Path,'/') as SubPath
Resident table2
Where not DepthDelta;

DROP Table table2;

tabResult:
LOAD 1 as TempField AutoGenerate 0;

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'tabTemp.*') THEN
  JOIN (tabResult) LOAD * RESIDENT [$(vTable)];
  DROP TABLE [$(vTable)];
  ENDIF
NEXT i

DROP Field TempField;

hope this helps

regards

Marco

[Anonymous]

Hi Marco,

Thanks for the help, your code works fine. I have a small change that needs to be done as the data has changed. In the same example, there is a chance where the data might be missing. For example, for the input below, the "End Schedule A" and " End Job A" are missing which is resulting in "Message B1" and "Message B2"  to associate to "Schedule A". I have tried to fix it myself but ending up getting the same result.

Field Name
Start Schedule A
Start Task A
Start Job A
Start File A
Message A1
Message A2
End File A
End Task A
Start Schedule B
Start Task B
Start Job B
Start File B
Message B1
Message B2
End File B
End Job B
End Task B
End Schedule B

Thanks in Advance.

[MarcoWedel]

Hi,

if at least your 'start' designators are reliable then you can do without the 'end' delimiters e.g. like this:

tabFieldHierarchy:
LOAD * Inline [
    Field
    Schedule
    Task
    Job
    File
    Message
];

mapFieldExtract: 
Mapping LOAD Field, '@start@'&Field&'@end@' Resident tabFieldHierarchy;

mapFieldNames: 
Mapping LOAD 'FieldName'&RecNo(), Field&' Name' Resident tabFieldHierarchy;

mapFieldDepth: 
Mapping LOAD Field, '@start@'&RecNo()&'@end@' Resident tabFieldHierarchy;

DROP Table tabFieldHierarchy;

table1:
Hierarchy (ID,ParentID,FieldName,,FieldName,Path)
LOAD *,
    SubField(IDPath,'/',-2) as ParentID;
LOAD *,
    Text(Left(Peek(IDPath),Index(Peek(IDPath)&'/','/',Depth-1)-1)&If(Depth>1,'/')&ID) as IDPath;
LOAD AutoNumber(RecNo(),'RowNo') as ID,
    [Field Name],
    Replace([Field Name],'Start ','') as FieldName,
    TextBetween(MapSubString('mapFieldExtract',[Field Name]),'@start@','@end@') as Field,
    TextBetween(MapSubString('mapFieldDepth',  [Field Name]),'@start@','@end@') as Depth   
FROM [https://community.qlik.com/thread/280301] (html, codepage is 1252, embedded labels, table is @3)
Where not [Field Name] like 'End *';

RENAME Fields using mapFieldNames;

hope this helps

regards

Marco