Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator III
Creator III

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
juraj_misina
Luminary Alumni
Luminary Alumni

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

View solution in original post

7 Replies
arulsettu
Master III
Master III

like this

Capture.PNG

berryandcherry6
Creator III
Creator III
Author

yes

juraj_misina
Luminary Alumni
Luminary Alumni

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
Master III
Master III

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
Creator III
Creator III
Author

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

arulsettu
Master III
Master III

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
Creator III
Creator III
Author

Thanks this is what i was looking for.