Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

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

    

idcampaign_idcomm_type_idsentdeliveredopendate_createddate_modified
123401-07-2017
223523301-07-201725-10-2017
42372501-07-201725-10-2017
523844501-07-201722-08-2017
6224451702-07-201702-07-2017
72245602-07-2017

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

  

idcampaign_idcomm_type_idsentdeliveredopendate_createddate_modified
123401-07-2017
223577701-07-201726-10-2017
423710501-07-201726-10-2017
523816141501-07-201726-10-2017
622447020702-07-201726-10-2017
722456302-07-201726-10-2017
822461226-10-2017

so my custom log Table should be

   

id campaign_id comm_type_id sent delivered open date_createddate_modified
1234 01-07-2017
223523301-07-201725-10-2017
323577701-07-201726-10-2017
423725 01-07-201725-10-2017
5237105 01-07-201726-10-2017
623844501-07-201722-08-2017
723816141501-07-201726-10-2017
8224451702-07-201702-07-2017
922447020702-07-201726-10-2017
1022456 02-07-2017
11224563 02-07-201726-10-2017
12224612 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.

1 Solution

Accepted Solutions
Luminary
Luminary

Re: custom Table to store records for each day in Qliksense

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

7 Replies
arulsettu
Honored Contributor III

Re: custom Table to store records for each day in Qliksense

like this

Capture.PNG

berryandcherry6
Contributor II

Re: custom Table to store records for each day in Qliksense

yes

Luminary
Luminary

Re: custom Table to store records for each day in Qliksense

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
Honored Contributor III

Re: custom Table to store records for each day in Qliksense

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
Contributor II

Re: custom Table to store records for each day in Qliksense

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

arulsettu
Honored Contributor III

Re: custom Table to store records for each day in Qliksense

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
Contributor II

Re: custom Table to store records for each day in Qliksense

Thanks this is what i was looking for.

Community Browser