topic Excel file updation using timestamps in QlikView

Excel file updation using timestamps

vikasmahajan — Mon, 08 Feb 2016 11:53:42 GMT

Dear all ,

I have 3 excel files , I stored into QVD (allqvd) . Now if same name file with diff. timestamps file copied then existing data from allqvd should be deleted and new data from new excel file should appended in allqvd file.

Please share logic.

Thanks in adv.

Vikas

Re: Excel file updation using timestamps

sunny_talwar — Mon, 08 Feb 2016 12:56:20 GMT

Not sure I completely understand what you are trying to do. Can you may be elaborate a little?

Re: Excel file updation using timestamps

vikasmahajan — Tue, 09 Feb 2016 03:15:45 GMT

Hi Sunny ,

Thanks for reply,

I have 3 excel files I have made full data qvd stored into qvd and kept it. Again suppose we received same 3 files with some modified records with different timestamps then I want to delete existing data from qvd for this 3 files and wants to append new data into full qvd file.

Hope you understand the requirement , Please let know if you require any thing.

This is urgent please give solution.

Thanks

Vikas

Re: Excel file updation using timestamps

HirisH_V7 — Tue, 09 Feb 2016 05:47:26 GMT

Hi,

Try Like this,

where Date(filetime(), 'DD/MM/YYYY') >= today()

For all of your Files such that you will get the data as of today() into your QVD.

HTH,

Hirish

Re: Excel file updation using timestamps

vikasmahajan — Tue, 09 Feb 2016 05:54:59 GMT

do you have script ???

Re: Excel file updation using timestamps

avinashelite — Tue, 09 Feb 2016 05:55:56 GMT

you could implement the Incremental loading concept rite? i.e. insert update and delete check the attached document

Re: Excel file updation using timestamps

HirisH_V7 — Tue, 09 Feb 2016 06:05:50 GMT

Hi,

Check this ,

PFA,

Hirish

Re: Excel file updation using timestamps

vikasmahajan — Tue, 09 Feb 2016 06:09:39 GMT

Thanks for your reply Actually I want to store all data into qvd and every time I want to update only latest modified file which will be possible through this script ?

Vikas

Re: Excel file updation using timestamps

vikasmahajan — Tue, 09 Feb 2016 06:33:32 GMT

Requirement :

1. I want to create fulldata qvd store all excels file say 12 excel files.

2. If user modify excel files and send for upload again then modified data should get updated

and existing stored data should get deleted.

3. if new file found append it .

Vikas

Re: Excel file updation using timestamps

manojkulkarni — Tue, 09 Feb 2016 06:43:27 GMT

Hi Vikas,

I think you should go with Incremental Load logic. Please check the below link & third scenario might help

https://www.resultdata.com/three-types-of-qlikview-incremental-loads/

Re: Excel file updation using timestamps

PradeepReddy — Tue, 09 Feb 2016 06:44:07 GMT

what is the time stamp field/modification date field you are considering for this process..?

Re: Excel file updation using timestamps

vikasmahajan — Tue, 09 Feb 2016 06:52:22 GMT

filetime() is a timestamps for this.

Vikas

Re: Excel file updation using timestamps

vikasmahajan — Tue, 09 Feb 2016 07:19:29 GMT

Sunny can you give script for the same ??

@Sunny T

Vikas

Re: Excel file updation using timestamps

vikasmahajan — Tue, 09 Feb 2016 07:31:26 GMT

please help

Re: Excel file updation using timestamps

sunny_talwar — Tue, 09 Feb 2016 10:44:02 GMT

Try the following code, seems to be working to some degree. May be you need to make some changes, but I guess a direction to look into

For Each file in 'AssetTrackingAug1.xlsx', 'AssetTrackingSep1.xlsx', 'AssetTrackingOct1.xlsx'

LET vVar = Replace(Right('$(file)', 9), '.xlsx', '') &'Time';

LET vTime = Num(FileTime('$(file)'));

Table:

LOAD Financial_Code,

Tower,

Family,

Set,

Offering,

Product,

Availability,

Price,

Segmentation,

Source_System,

charge_month,

inventory_month,

FileName() as FileName,

FileTime() as FileTime

FROM

$(file)

(ooxml, embedded labels, table is Sheet);

Main:

LOAD Financial_Code,

Financial_Code as FC,

Tower,

Family,

Set,

Offering,

Product,

Availability,

Price,

Segmentation,

Source_System,

charge_month,

inventory_month,

FileName,

FileTime

FROM

Table.qvd

(qvd)

Where FileName = '$(file)' and FileTime <= $(vTime);

NEXT

FinalTable:

NoConcatenate

LOAD *

Resident Main;

Concatenate(FinalTable)

LOAD *

Resident Table

Where not Exists(FC, Financial_Code);

DROP Field FC;

STORE Table into Table.qvd (qvd);

DROP Tables Table, Main;

Re: Excel file updation using timestamps

PradeepReddy — Tue, 09 Feb 2016 10:56:22 GMT

see the attachment, it might be helpful...

Re: Excel file updation using timestamps

vikasmahajan — Tue, 09 Feb 2016 11:13:48 GMT

While loading this script i am getting this error ?

Re: Excel file updation using timestamps

PradeepReddy — Tue, 09 Feb 2016 11:24:53 GMT

I have assumed( Filkes given by you) your file names like..

File Name: AssetTrackingAug1; SheetName: AugInv1

File Name: AssetTrackingSep1; SheetName: SepInv1

If there is any changes apart from these formats, then we need to change our script accordingly.

Can you share exact file names and Sheet names, so that will try to resolve the issue.

Re: Excel file updation using timestamps

vikasmahajan — Tue, 09 Feb 2016 11:28:33 GMT

ok no problem i will rename and check pls wait.

Re: Excel file updation using timestamps

vikasmahajan — Tue, 09 Feb 2016 11:32:45 GMT

Thanks Pradeep

This script is worked as per expected.

Vikas.