
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
custom Table to store records for each day in Qliksense
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.
Accepted Solutions
.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
like this

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
yes
.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
EmailMetricsTable is single table, i have shown how data looks on day 25th and day 26th as an example.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks this is what i was looking for.
