custom Table to store records for each day in Qliksense

[berryandcherry6]

I have EmailMetricsTable where data will be updated every day by overriding datas. My requirement is to get records for each day and log records in other customTable, for each day.

EmailMetricsTable:

On day 25 oct EmailMetricsTable looks like

id	campaign_id	comm_type_id	sent	delivered	open	date_created	date_modified
1	23	4				01-07-2017
2	23	5	2	3	3	01-07-2017	25-10-2017
4	23	7	2	5		01-07-2017	25-10-2017
5	23	8	4	4	5	01-07-2017	22-08-2017
6	224	4	5	1	7	02-07-2017	02-07-2017
7	224	5	6			02-07-2017

on day 26 oct EmailMetricsTable looks like (observe that date_modified is changed and sent,delivered, open are update)

id	campaign_id	comm_type_id	sent	delivered	open	date_created	date_modified
1	23	4				01-07-2017
2	23	5	7	7	7	01-07-2017	26-10-2017
4	23	7	10	5		01-07-2017	26-10-2017
5	23	8	16	14	15	01-07-2017	26-10-2017
6	224	4	70	20	7	02-07-2017	26-10-2017
7	224	5	6	3		02-07-2017	26-10-2017
8	224	6	12			26-10-2017

so my custom log Table should be

id	campaign_id	comm_type_id	sent	delivered	open	date_created	date_modified
1	23	4				01-07-2017
2	23	5	2	3	3	01-07-2017	25-10-2017
3	23	5	7	7	7	01-07-2017	26-10-2017
4	23	7	2	5		01-07-2017	25-10-2017
5	23	7	10	5		01-07-2017	26-10-2017
6	23	8	4	4	5	01-07-2017	22-08-2017
7	23	8	16	14	15	01-07-2017	26-10-2017
8	224	4	5	1	7	02-07-2017	02-07-2017
9	224	4	70	20	7	02-07-2017	26-10-2017
10	224	5	6			02-07-2017
11	224	5	6	3		02-07-2017	26-10-2017
12	224	6	12			26-10-2017

How could i create custom Log table like this. Any idea or help will be appreciated. Is it possible to do like this?

Please help me on this.

[arulsettu]

like this

[berryandcherry6]

yes

[juraj_misina · Accepted solution]

Hi,

you need to store the table every day into a QVD file. Your scrript might look something like this:

EmailMetricsTable:
LOAD
     id,
     campaign_id,
     comm_type_id,
     sent,
     delivered,
     open,
     date_created,
     date_modified
From [source]
Where date_created=Today() OR date_modified=Today() //comment out this line for initial load to get the whole file
;

If(FileSize('EmailMetricsTable.qvd')>0) then
Concatenate('EmailMetricsTable')
LOAD
     *
From [EmailMEtricsTable.qvd] (qvd);

Store EmailMetricsTable into [EmailMEtricsTable.qvd] (qvd);

This will load fresh data from your data source, attach previously loaded data from a qvd file and overwrite that qvd file with a new - combined - table.

Hope this helps.

Juraj

[arulsettu]

you can concatenate both tables

day25:

load * Inline [

id, campaign_id,comm_type_id,sent,delivered,open,date_created,date_modified

1, 23, 4, , , , 01-07-2017,

2, 23, 5, 2, 3, 3, 01-07-2017, 25-10-2017

4, 23, 7, 2, 5, , 01-07-2017, 25-10-2017

5, 23, 8, 4, 4, 5, 01-07-2017, 22-08-2017

6, 224,4, 5, 1, 7, 02-07-2017, 02-07-2017

7, 224 ,5, 6, , , 02-07-2017,

];

Concatenate(day25)

day26:

load * Inline [

id,campaign_id,comm_type_id,sent,delivered,open,date_created,date_modified

1, 23, 4, , , , 01-07-2017,

2, 23, 5, 7, 7, 7, 01-07-2017, 26-10-2017

4, 23, 7, 10, 5, , 01-07-2017, 26-10-2017

5, 23, 8, 16, 14, 15, 01-07-2017, 26-10-2017

6, 224,4, 70, 20, 7, 02-07-2017, 26-10-2017

7, 224,5, 6, 3, , 02-07-2017, 26-10-2017

8, 224,6, 12, , , 26-10-2017,

];

[berryandcherry6]

EmailMetricsTable is single table, i have  shown how data looks on day 25th and day 26th as an example.

[arulsettu]

you can create two tables by applying some condition like

Where date_created=Today()

and another table like

Where date_created=Today()-1

concatenate both

[berryandcherry6]

Thanks this is what i was looking for.