topic Re: Multiple values in single column in App Development

Multiple values in single column

jpjust — Thu, 05 Aug 2021 14:14:25 GMT

Hi,

I have an table as below . Basically I tagged dataconnections in QMC.

Dataconnectionname tag

ABC Europe

ABC US

I want the table as below. (Tag values in one row)

Dataconnectionname tag

ABC Europe, US

Following is part of the script that output the above table.

Tags:

LOAD
[__KEY_root],
concat([name_tag] , ',') as Tag.
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);

[DataConnections]:

LOAD
[__KEY_root],
[DataConnectionName]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

I tried this concat([name_tag] , ',') in script but did not work with invalid expression.

Please let me know how to resolve this.

Thanks

Re: Multiple values in single column

Taoufiq_Zarra — Thu, 05 Aug 2021 14:37:07 GMT

@jpjust with concat you must add group by

for example :

Input: LOAD * INLINE [ Dataconnectionname,tag ABC,Europe ABC,US ]; output: noconcatenate load Dataconnectionname,concat(tag,',') as tag resident Input group by Dataconnectionname; drop table Input;

the output:

Re: Multiple values in single column

jpjust — Fri, 06 Aug 2021 14:58:21 GMT

Thank you so much.

My situation is like this. Please let me know how to accomplish.

[DataConnections]:
LOAD [id_u2] AS [DataConnectionID],
[name_u1] AS [DataConnectionName],
[__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull ([__KEY_root]);

[tags]:
LOAD
[id_u7],
[__FK_tags] AS [__KEY_root],
[name_tag] AS tag
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);

Now, in the above script, how can I do an group by and get the table output that I need.

The common field between both the table is [__KEY_root]

Dataconnectionname tag

ABC Europe, US

Re: Multiple values in single column

Taoufiq_Zarra — Fri, 06 Aug 2021 15:05:47 GMT

Maye be like :

DataConnections:
LOAD [id_u2] AS [DataConnectionID],
[name_u1] AS [DataConnectionName],
[__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull ([__KEY_root]);

left join

LOAD
[id_u7],
[__FK_tags] AS [__KEY_root],
[name_tag] AS tag
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tags]);

output:
noconcatenate

load DataConnectionName,concat(tag,',') as tag resident DataConnections group by DataConnectionName;

drop table DataConnections;

Re: Multiple values in single column

jpjust — Fri, 06 Aug 2021 15:24:55 GMT

That worked. Really, Thanks much.