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: 
HKN1
Contributor III
Contributor III

A difficult table format requested

Hello,

I have a green table below.

I want to get out like a red table, but I couldn't. I think I have to solve this on the script side.

Could you help?

 

Thanks.

 

HKN1_0-1719922479494.png

 

Labels (1)
1 Solution

Accepted Solutions
marksouzacosta

This is a very unusual request, but yes, you can do that. Following an example:

TempSourceData:
LOAD 
    FormNo,
    Onwer,
    Responsible,
    Date
INLINE [
FormNo, Onwer, Responsible, Date
1, Owner1, Resp1, 6/24/2024
1, Owner1, Resp2, 6/25/2024
1, Owner1, Resp3, 6/26/2024
2, Owner2, Resp1, 6/12/2024
2, Owner2, Resp5, 6/13/2024
];

NoConcatenate

TempIndexes:
LOAD
	FormNo,
    Onwer,
    Responsible,
    Date,
    If(Not Match(FormNo, Peek(FormNo)),10102,Peek(Index)+1) AS Index
RESIDENT
	TempSourceData
ORDER BY
     FormNo,
     Date
;

DROP TABLE TempSourceData;



Final:
LOAD
	FormNo,
    Onwer & ' ' & CONCAT(Chr(Index) & ' ' & Responsible,'',Date) AS Responsible_ALL
RESIDENT
	TempIndexes
GROUP BY
	FormNo,
    Onwer
;

DROP TABLE TempIndexes;

This is the output of my example:

marksouzacosta_0-1719934625196.png

 

Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

View solution in original post

4 Replies
marksouzacosta

I would do that on Load Script.

To get the Responsible_ALL field you can use CONCAT:
Concat - script function | QlikView Help

It will be something lik this:

Owner & ' > ' & Concat(Responsible,' > ',CompletedDate) AS Responsible_ALL

 All the other fields you can get with the appropriate Max Aggregations.

Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

HKN1
Contributor III
Contributor III
Author

Thanks.

 

How can I use it instead?

 

' > ' 

 

chr(10102) .......

 

 

HKN1_0-1719931988931.png

 

marksouzacosta

This is a very unusual request, but yes, you can do that. Following an example:

TempSourceData:
LOAD 
    FormNo,
    Onwer,
    Responsible,
    Date
INLINE [
FormNo, Onwer, Responsible, Date
1, Owner1, Resp1, 6/24/2024
1, Owner1, Resp2, 6/25/2024
1, Owner1, Resp3, 6/26/2024
2, Owner2, Resp1, 6/12/2024
2, Owner2, Resp5, 6/13/2024
];

NoConcatenate

TempIndexes:
LOAD
	FormNo,
    Onwer,
    Responsible,
    Date,
    If(Not Match(FormNo, Peek(FormNo)),10102,Peek(Index)+1) AS Index
RESIDENT
	TempSourceData
ORDER BY
     FormNo,
     Date
;

DROP TABLE TempSourceData;



Final:
LOAD
	FormNo,
    Onwer & ' ' & CONCAT(Chr(Index) & ' ' & Responsible,'',Date) AS Responsible_ALL
RESIDENT
	TempIndexes
GROUP BY
	FormNo,
    Onwer
;

DROP TABLE TempIndexes;

This is the output of my example:

marksouzacosta_0-1719934625196.png

 

Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

HKN1
Contributor III
Contributor III
Author

Hi @marksouzacosta 

 

Thank you very much.

Great, it worked.

 

🙏