Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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

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 NameTask NameJob NameFile NameMessage
Schedule ATask AJob AFile A

Message A1

Schedule ATask AJob AFile A

Message A2

Schedule BTask BJob BFile BMessage B1
Schedule BTask BJob BFile BMessage 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.

5 Replies
Highlighted

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

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

Highlighted
Creator
Creator

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

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

Highlighted

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

Hi,

one solution might be:

QlikCommunity_Thread_280301_Pic1.JPG

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

Highlighted
Creator
Creator

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

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.

Highlighted

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

Hi,

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

QlikCommunity_Thread_280301_Pic2.JPG

QlikCommunity_Thread_280301_Pic3.JPG

QlikCommunity_Thread_280301_Pic4.JPG

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