topic Re: Table Design in App Development

Table Design

jpjust — Tue, 04 May 2021 16:38:44 GMT

Hi All,

I have a table below called usage

ID Last Used

1 01/03/2020

2 01/05/2021

Table1:

SQL SELECT
"id",
"lastUsed",

From xxxxxtable

Now I am bringing an qvd to the script editor. This qvd brings the same data structure as above but different data.

LEFT JOIN (Table1) LOAD * from [table2.QVD](qvd);

Now the above table is updated as below

ID Last Used

1 01/03/2020

1 04/08/2021

2 01/05/2021

2 04/28/2021

But I want the table in below format

ID Last Used LastUsed

1 01/03/2020 04/08/2021

2 01/05/2021 04/28/2021

Any help will be appreciated!

Thanks

Re: Table Design

rwunderlich — Tue, 04 May 2021 17:54:32 GMT

Change the Join Load to use an alias for Last Used:

LEFT JOIN (Table1) LOAD
ID,
[Last Used] as LastUsed
from [table2.QVD](qvd);

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Table Design

jpjust — Tue, 04 May 2021 20:22:41 GMT

Thanks. That works great!

Re: Table Design

jpjust — Tue, 04 May 2021 20:26:18 GMT

One more formatting question.

I have join between multiple tables and at the end I have one table as below in the app

Name Tags

Michael Banking

Michael Mortgage

Michael Forex

Is there a way that I can be able to show in a single record like below?

Name Tags

Michael Banking, Mortgage,Forex

Thanks

Re: Table Design

jpjust — Tue, 04 May 2021 20:44:51 GMT

I tried some thing like this using concat but it throws expression error on the script.

Concat(SubField([name_u0], ' ', 1 ),',',SubField([name_u0], ' ', 2)) as NEWTag

Thanks

Re: Table Design

rwunderlich — Tue, 04 May 2021 20:54:25 GMT

Using the first Name/Tags table shown above as input, the load would be like:

NewTags:
LOAD
Name,
Concat(Tags, ',') as NewTag
Resident TagTable
Group By Name;

-Rob