How to add Date automatically to the data by updating on daily basis

[rkpatelqlikview]

Hi Experts.

Please suggest on this.

I do have source date in excel. It is appending on daily basis so for that am adding the column manually in excel.

But instead of adding the column in excel for date, i want generate the column along with the date in qlikview itself.

Example: Today date is 15/6/2018.  so today am appending the data then the yesterday date should be generate to the data. i.e.,(14/6/2018)

Thanks in advance.

[sunny_talwar]

Just add Date(Today() - 1) as Date in the load script before you append it

[rkpatelqlikview]

Thanks for your respond Sunny,

I did it in my script. If i append tomorrow data then that previous data is removed.

Example:

If i append the 14th data on 15th morning,Again if i append the 15th data on 16th. it should be remain the same.

Today()-1 will remove that date again it will add fresh add. But i need like above.

Thanks

RUNIL KUMAR

[captain89]

Hi Kumar,

I don't know if I understand correctly.

You have an excel file with Sname and SID.. and you need to create a field with the date.

The solution of Sunny il correct.

Maybe ... Do you also need to store data of previuos days?

you can do it:

//load the excel

tab:

LOAD Date(today()-1) as Date,

     SName,

     Sid

FROM

[sourceexcel.xlsx]

(ooxml, embedded labels, table is Sheet1);

//create a variable

let vDate=year(today())*10000+month(today())*100+day(today())*1;

store tab into tab$(vDate).qvd (qvd);

drop table tab;

//load data from qvds

tabx:

LOAD Date,

     SName,

     Sid

FROM [tab*.qvd] (qvd);

[sunny_talwar]

If you are appending how is old data getting removed?

[rkpatelqlikview]

Thanks Matteo,

I agreed with Sunny and you.

Your suggested script might be work. wil check

Just my small concern is, Obviously the data will be to add the data.Next day will add the data of the previous day at that time the date column should be take it as previous day date as well as previous of previous date  data remains the same.

Ex:This data Sno, SID appended on 5/15/2018.

Next day data came and appended and reloaded then date 5/16/2018 should be added automatically.

If i use this Today()-1. it will be replace the 5/15/2018. Thanks again for sunny and Matteo.

[captain89 · Accepted solution]

Hi Kumar,

Is there a primary key field? Because you can exclude from the reload the rows that are already in the excel using this key...

If the excel comes from the sistem you may load every day a new excel with new data...

But you must save into qvds bacause qlik can't know the Date of the rows if it's not write in a field.

In my solution I I thought that you delate the lines of the previuos day... but maybe is not that the case.

If Sno means Serial Number and it has a progressive logic you can load only the rows after the max of the old data. So the first day

//the first day

tab:

LOAD Date(today()-1) as Date,

     SName,

     Sid

FRO

[sourceexcel.xlsx]

(ooxml, embedded labels, table is Sheet1);

store tab into tab.qvd (qvd);

//the second day and the others:

id_max:

load

max(Sid) as IdMax from tab.qvd (qvd);

let vMaxId = FieldValue ('IdMax', 1) ;

drop table id_max;

tab2:

load

Date, SName, Sid FROM tab.qvd (qvd);

concatenate

LOAD Date(today()) as Date,

     SName,

     Sid

FROM

[sourceexcel.xlsx]

(ooxml, embedded labels, table is Sheet1) where Sid > '$(vMaxId)';

[rkpatelqlikview]

Hi Sunny,

Old data not removed, am saying Date will be removed. 

Date(today()-1) as Date, This will take yesterday data but might be missed the date which before yesterday.

For that Before yesterday data will be assign to yesterday Date.

As suggested by Matteo, hope that is the great suggestion to do this.

Please correct me if am wrong.

[rkpatelqlikview]

Hi Matteo,

This looks good. Will try it from my end. and let you know the status how it is working.

Sunny/Matteo,

Thanks for your great responses

[moojahid2]

At data load editor
Let vstartdate = date(today()- 31,'YYYY-MM-DD'); // 30 days from yesterday
Let venddate = date(today()- 1,'YYYY-MM-DD');// to yesterday
now plz use startdate=$(vstartdate) and
enddate=$(venddate)