Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
lucasdavis500
Contributor II

Re: Incremental Update/Insert with External Data (NO DB Connection)

This cannot be true. You can manually create a DateModified field as long as it is greater than my previous DateModified for the same data, it will overwrite it. I'm doing a "full reload" of the previous month essentially, overwriting data that has changed and has not changed, but not a "full reload" in the sense that I'm not wiping my QVD. My QVD will still have previous months data, just a 1 month lag should get a full wipe and reload. Does this make sense?

Highlighted
vinieme12
Esteemed Contributor II

Re: Incremental Update/Insert with External Data (NO DB Connection)

You are creating a date modified field using the now function! the actual datemodifed in terms of DATA should ideally flow from the from end CRM tool / Software which relates to each data row

In this sense what you are adding is not a modified date for the record, but a date that you refreshed it which is like a dateadded flag.

I understand your logic; you do not want to refresh all of the data by doing this, but have you tried yet? There will not be much difference in reload time.

Which is why I recommend to drop your where clause comparing with your date field.

Just try as below

CET:
LOAD
Pkey,
State,
AccountType,
DisputeType,
TaxID,
Mode,
AssignedTo,
ResolvedBy,
DateResolved,
DATE(DateCreated) as DateCreated,
ProductLine,
CreditDisputeType

FROM

[$(vPathname)CET_$(vDataDate).xlsx]
(
ooxml, embedded labels, table is Sheet1);


IF NOT ISNULL(QVDCREATETIME('$(vQVDPath)CETData.qvd')) THEN

CET_Final:
CONCATENATE(CET)
LOAD
Pkey,
State,
AccountType,
DisputeType,
TaxID,
Mode,
AssignedTo,
ResolvedBy,
DateResolved,
DATE(DateCreated) as DateCreated,
ProductLine,
CreditDisputeType

FROM $(vQVDPath)CETData.qvd (QVD)
WHERE NOT(EXISTS (Pkey)) ;
END IF

IF NoOfRows(CET) > 0 THEN

STORE CET_FINAL INTO $(vQVDPath)CETData.qvd (QVD) ;

//DROP TABLE CET ;
END IF ;

lucasdavis500
Contributor II

Re: Incremental Update/Insert with External Data (NO DB Connection)

Vineeth, the artificial DateModified field isn't really an option in my case. I'll explain. The data I use comes from a Credit Bureau, I cannot force an outside source to create a "Date Modified" field. The data I get from the CB is updated every time a "resolution" has been determined regarding one of our customers. There is a time frame in which these updates must be made, therefore I know when I can pull and essentially get "all" of the stages of this customers lifecycle with the CB, but I only care about the most recent updates made. Our option is to pull from the CB multiple times a month, for the previous month, in order to capture the lifecycle.

I.E.

Customer        DateCr         DateRes

123                 9/15/2016           -                                         >>>>> ON 10/1

123                 9/15/2016           -                                         >>>>> ON 10/4

123                 9/15/2016      10/14/2016                             >>>>> ON 10/14

I don't think you understand my scenario fully. What I'm basically doing by adding my own DateModified field is essentially wiping everything from the last 30 days and reloading it.

Please explain the issues you see arising from me synthetically creating this DateModified so I can assess..

lucasdavis500
Contributor II

Re: Incremental Update/Insert with External Data (NO DB Connection)

I guess what I'm saying, is the only way to get "DateModified", a true date, would be if ALL THREE of the major CB agencies changed their reporting tool, which I highly doubt they would do for me, so I have to create one after the fact....

lucasdavis500
Contributor II

Re: Incremental Update/Insert with External Data (NO DB Connection)

The reason I cannot do a full reload is because this data is not stored for long periods of time, so unless I manually go into excel, append my data for a "historical" file, then keep reloading, this won't work. Right now, I wouldn't be able to pull January data for my report, I can only go back a few months.... I don't see how your code would "Update" old records of mine. The method you chose would not update a record that was added in my October 1st pull, but updated on my 10/10 pull. (Again, No DateModified field here, file would be the same as the first pull with maybe a value added into one of the fields that had no previous value) It would look at the Pkey and say "HEY, We've got one of those, we don't need a new one", unless I had some Date field that told QV, "Hey, I know we've got one of those, but he needs to be replaced because he's old". So I must tell QV to replace regardless if he was updated or not, because it's the "Latest" data....

vinieme12
Esteemed Contributor II

Re: Incremental Update/Insert with External Data (NO DB Connection)

Can you provide a sample in Excel that replicates your scenario?

MVP & Luminary
MVP & Luminary

Re: Incremental Update/Insert with External Data (NO DB Connection)

Like Dirk said, discard the date logic all together. 

1. Load the entire new monthly xlsx file.

2 Load the QVD with "where not exists(Pkey)".

Your original script as posted should work as-is if you just remove the WHERE clause from the load of the xlsx file.

-Rob

lucasdavis500
Contributor II

Re: Incremental Update/Insert with External Data (NO DB Connection)

Today, what Vineeth had said, and what you have said, just hit me. I understand why you say that I don't need the where clause...

MVP & Luminary
MVP & Luminary

Re: Incremental Update/Insert with External Data (NO DB Connection)

I had not noticed Vineeth's reply. It's quite correct, and I hope you mark it as such.

-Rob