Task categories Status with Log ID in one excel with multiple status as header

[nicholas5141]

Hi,

I need help on this excel with the status as header link with log ID. Is it possible to do that ?

Excepted Result:

Log ID     Descriptions     Date     Status

1              test1                           Open      

2              test2                           Open

3              test3                           Open

4              test4                           WIP      

5              test5                           WIP

6              test6                           WIP

7              test7                           Close      

8              test8                           Close

9              test9                           Close

If there is any better approach please suggest!

Thank you

[settu_periasamy]

One solution could be..

Directory;
T1:
LOAD [Log ID],
Descriptions,
Date,
if(WildMatch([Log ID],'*Status*'),SubField([Log ID],': ',2),Peek(Status)) as Status
FROM
QlikCommunityFile.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

NoConcatenate
T2:
LOAD * Resident T1 Where not IsNull(Descriptions);

DROP Table T1;

[tamilarasu]

You can also use transformation wizard to achieve the same.

T1:
LOAD [Log ID],
Descriptions,
Date(Date) as Date,
Replace(F4,'Status: ','') as Status
FROM
QlikCommunityFile.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(
ColXtr(1, RowCnd(CellValue, 1, StrCnd(start, 'Status:')), 0),
Replace(4, top, StrCnd(null)),
Remove(Row, RowCnd(CellValue, 1, StrCnd(start, 'Status:')))
));

[nicholas5141]

Hi, do you mind show the steps?

Thanks

[tamilarasu · Accepted solution]

Sure. I just pasted the screenshots in a word file and attached the same. If you find any difficulties, let me know.

[nicholas5141]

Thanks! I got It