Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
nicholas5141
Partner - Creator
Partner - Creator

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

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

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

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

View solution in original post

5 Replies
settu_periasamy
Master III
Master III

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;

Capture.JPG

tamilarasu
Champion
Champion

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:')))
));


Capture.PNG

nicholas5141
Partner - Creator
Partner - Creator
Author

Hi, do you mind show the steps?

Thanks

tamilarasu
Champion
Champion

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

nicholas5141
Partner - Creator
Partner - Creator
Author

Thanks! I got It