Table Design

[jpjust]

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

[rwunderlich · Accepted solution]

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

[jpjust]

Thanks. That works great!

[jpjust]

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

[jpjust]

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

[rwunderlich]

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