topic Re: Combining 3 charts back to 1 in QlikView

Combining 3 charts back to 1

lukegilligan — Sat, 16 Nov 2024 18:16:01 GMT

Was wondering if anyone may have a solution on how I cat re-sort 3 extracted tables back in to one table with all the timeline events sorted in to columns?

I have the following table:

Asset	effectiveDate	eventID	summary	code
123	30/12/2019	109653.0001	From org. '109690' to '[VUT]'. Automatic tenant vacate: Vacate property from tenanted to [void/VUT]	[VUT]
123	22/01/2020	109657.0001	From Org. '[VUT]' to '[Vacant]'	[VACANT]
123	29/01/2020	109681.0001	From Org. '[Vacant]' to '109599'. Automatic tenant move: Tenancy status changed from [Vacant] to [Tenanted]	109599
123	21/06/2020	110726.0001	From org. '109599' to '[VUT]'. Automatic tenant vacate: Vacate property from tenanted to [void/VUT]	[VUT]
123	30/06/2020	110839.0001	From Org. '[VUT]' to '[Vacant]'	[VACANT]
123	8/07/2020	110840.0001	From Org. '[Vacant]' to '109870'. Automatic tenant move: Tenancy status changed from [Vacant] to [Tenanted]	109870

This has been split in to the following 3 tables depending on the code

VUT

Asset	VUT.effectiveDate	VUT.eventID	VUT.summary	VUT.code
123	30/12/2019	109653.0001	From org. '109690' to '[VUT]'. Automatic tenant vacate: Vacate property from tenanted to [void/VUT]	[VUT]
123	21/06/2020	110726.0001	From org. '109599' to '[VUT]'. Automatic tenant vacate: Vacate property from tenanted to [void/VUT]	[VUT]

VAC

Asset	VAC.effectiveDate	VAC.eventID	VAC.summary	VAC.code
123	22/01/2020	109657.0001	From Org. '[VUT]' to '[Vacant]'	[VACANT]
123	30/06/2020	110839.0001	From Org. '[VUT]' to '[Vacant]'	[VACANT]

TEN

Asset	TEN.effectiveDate	TEN.eventID	TEN.summary	TEN.code
123	29/01/2020	109681.0001	From Org. '[Vacant]' to '109599'. Automatic tenant move: Tenancy status changed from [Vacant] to [Tenanted]	109599
123	8/07/2020	110840.0001	From Org. '[Vacant]' to '109870'. Automatic tenant move: Tenancy status changed from [Vacant] to [Tenanted]	109870

My aim is to get to a table like below:

Asset	VUT.eventID	VUT.effectiveDate	VAC.eventID	VAC.effectiveDate	TEN.eventID	TEN.effectiveDate
123	109653.0001	30/12/2019	109657.0001	22/01/2020	109681.0001	29/01/2020
123	110726.0001	21/06/2020	110839.0001	30/06/2020	110840.0001	8/07/2020

I have no idea on how to associate the data tables back to rows where all I have to work with is an incremental date + EventID.

Any help would be very much appreciated.

Re: Combining 3 charts back to 1

lukegilligan — Fri, 07 Aug 2020 05:00:49 GMT

Anyone have any thoughts on this at all? Even just the right direction to start looking?

Re: Combining 3 charts back to 1

rushikale0106 — Fri, 07 Aug 2020 07:25:52 GMT

Hello @lukegilligan ,

I don't know how much will it help you but if you need a direction to achieve the target table, then I will suggest you understand Joins.

Specifically, Inner Join. Try to achieve it with the SQL query first.

Since the common column is present then it will be easier to get data in one table again.

Let me know if you need more help.

Regards,

Rushi

Re: Combining 3 charts back to 1

lukegilligan — Mon, 10 Aug 2020 02:36:28 GMT

Thanks @rushikale0106 I understand the concept of joins, I am just struggling to find a way to associate 3 different states to the one row.

I am guess I need to somehow use the order of the property, then the eventID to make a key somehow, but am stuck to work out how.

Thanks for the suggestion though.

Re: Combining 3 charts back to 1

Brett_Bleess — Tue, 18 Aug 2020 19:12:26 GMT

I believe the only means of doing this would be Set Analysis in the table chart, here are some links that may be of some help, not sure if this is how you wanted to go at things or not, it might help in this case if you attach your QVW file, so folks can see the data model etc., as that will likely help in coming up with ideas on how to approach things too.

Design Blog area:
https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344
https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Help Link:
https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

Sorry I do not have anything better, but my post will kick things back up, so someone else may see it again and try to offer something else, but I think you may need to provide a bit more information first if possible.

Regards,
Brett

Re: Combining 3 charts back to 1

Kushal_Chawda — Tue, 18 Aug 2020 20:56:00 GMT

checkout generic load option

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptPrefixes/Generic.htm

Re: Combining 3 charts back to 1

Saravanan_Desingh — Tue, 18 Aug 2020 22:10:35 GMT

One solution is.

VUT: LOAD RowNo() As RowID,* INLINE [ Asset, VUT.effectiveDate , VUT.eventID , VUT.summary , VUT.code 123, 30/12/2019, 109653.0001, "From org. '109690' to '{VUT}'. Automatic tenant vacate: Vacate property from tenanted to {void/VUT}", "{VUT}" 123, 21/06/2020, 110726.0001, "From org. '109599' to '{VUT}'. Automatic tenant vacate: Vacate property from tenanted to {void/VUT}", "{VUT}" ]; Left Join(VUT) VAC: LOAD RowNo() As RowID,* INLINE [ Asset, VAC.effectiveDate , VAC.eventID , VAC.summary , VAC.code 123, 22/01/2020, 109657.0001, "From Org. '{VUT}' to '{Vacant}'", "{VACANT}" 123, 30/06/2020, 110839.0001, "From Org. '{VUT}' to '{Vacant}'", "{VACANT}" ]; Left Join(VUT) TEN: LOAD RowNo() As RowID,* INLINE [ Asset, TEN.effectiveDate , TEN.eventID , TEN.summary , TEN.code 123, 29/01/2020, 109681.0001, "From Org. '{Vacant}' to '109599'. Automatic tenant move: Tenancy status changed from {Vacant} to {Tenanted}", 109599 123, 8/07/2020, 110840.0001, "From Org. '{Vacant}' to '109870'. Automatic tenant move: Tenancy status changed from {Vacant} to {Tenanted}", 109870 ];

Re: Combining 3 charts back to 1

Saravanan_Desingh — Tue, 18 Aug 2020 22:11:30 GMT

Output.