topic Re: create new table based on existing table in App Development

create new table based on existing table

Suxel — Mon, 06 Sep 2021 11:07:24 GMT

Hi,

I would like to create a new table from an existing details table (qvd) that already stored in the application.

The existing table has all activities each user participated.

Date	User	Team	Tasks	Activities
03.09.2021	Tina	Team1	TaskA	Activity1
04.09.2021	Tina	Team1	TaskA	Activity2
04.09.2021	Tina	Team1	TaskA	Activity3
04.09.2021	Tina	Team1	TaskA	Activity4
04.09.2021	Tina	Team1	TaskA	Activity5
04.09.2021	Tina	Team1	TaskA	Activity6
04.09.2021	Judy	Team2	TaskB	Activity1
04.09.2021	Judy	Team2	TaskB	Activity2
04.09.2021	Judy	Team2	TaskB	Activity3
04.09.2021	Alex	Team1	TaskA	Activity1
04.09.2021	Alex	Team1	TaskA	Activity2
04.09.2021	Alex	Team1	TaskA	Activity3
04.09.2021	Alex	Team1	TaskA	Activity4
03.09.2021	Bobby	Team2	TaskB	Activity1
04.09.2021	Bobby	Team2	TaskB	Activity2
04.09.2021	Bobby	Team2	TaskB	Activity3
04.09.2021	Bobby	Team2	TaskB	Activity4
04.09.2021	Bobby	Team2	TaskB	Activity5
04.09.2021	Bobby	Team2	TaskB	Activity6

In my expected result, each user should only appear once a day and if user participates in activity 6 then 50 points, if not 100 points. This new table will be store as another qvd.

Date	User	Team	Points
03.09.2021	Tina	Team1	100
03.09.2021	Bobby	Team2	100
04.09.2021	Tina	Team1	50
04.09.2021	Judy	Team2	100
04.09.2021	Alex	Team1	100
04.09.2021	Bobby	Team2	50

Could anyone help to write the load script?

Re: create new table based on existing table

Suxel — Wed, 08 Sep 2021 11:04:06 GMT

I have tried to write below script, it seems working. With below script [libA] [libB] are synthetic with "Date", "User", "Team", if "Activities" is filter in chart A, then chart B with "Points" display will go off. How can I remain chart B "Points" display with "Activities" is filter in chart A ?

[Result100]:
LOAD Distinct
"Date",
"User",
"Team",
100 as "Points",
Date("Date",'YYMMDD') & '-' & "User" as "myID"
from [libA] (qvd) where not match ("Activities", 'Activity6');

Concatenate
Load * from [libB] (qvd)
Where not Exists("myID");

Store Result100 into [libB] (qvd);

Drop Table [Result100];

---------------------------------------

[Result50]:
LOAD Distinct
"Date",
"User",
"Team",
50 as "Points",
Date("Date",'YYMMDD') & '-' & "User" as "myID"
from [libA] (qvd) where match ("Activities", 'Activity6');

Concatenate
Load * from [libB] (qvd)
Where not Exists("myID");

Store Result50 into [libB] (qvd);

Drop Table [Result50];

---------------------------------------

Load [libA] (qvd);

Load [libB] (qvd);

Re: create new table based on existing table

NitinK7 — Wed, 08 Sep 2021 12:39:56 GMT

might be don't do any connection between two table

rename all new table fields or try to use qualify *

Re: create new table based on existing table

Suxel — Tue, 21 Sep 2021 12:00:15 GMT

at the end, I have added an expression on the chart B measures.

Sum({$<[Activities] = >} [Points])