Loading error with same column name from QVD using Where Clause

[poluvidyasagar]

Hi,

I have 2 qvd files with date column in it and trying to use the following logic for my incremental load problem. I am trying to load the data from both the qvds with where clause like below:

So I begin with setting my current todaysweek to WeekToday variable

LET WeekToday= Year(today()) & Num(Week(today()),'00')

Now I load the data from Table1 like below.  Trying to load anything not equal to current week data. The first table works fine.

Table1:

LOAD Date, A, B
FROM [$(vL.QVDFolder)Table1.qvd](qvd)
Where Year(Max(Date)) & Num(Week(Max(Date)),'00') <> '$(WeekToday)';

Now I am trying to load table 2 just like above from qvd with the same logic. But this time Table2  has Date column and 2 different columns C, D. Both tables are entirely different so I dont want to concatenate and also I do not want to use qualify statement.

Table2:
LOAD Date, C,D
FROM [$(vL.QVDFolder)Table2.qvd](qvd)
where Year(Max(Date)) & Num(Week(Max(Date)),'00') <> '$(WeekToday)';

This time I am getting an error saying :

"Unknown LOAD statment error
Table2:
LOAD Date, C,D
FROM [..\2.QVD\ManageLocationHistory.qvd](qvd)
where Year(Max(Date)) & Num(Week(Max(Date)),'00') <> '$(WeekToday)'"

Is it a problem to load data from 2 qvds with same date column field and use them as where clause.

Thanks,
Vidya

[sunny_talwar]

What happens when you only Load Table2 alone... does it go through then without any issues?

[poluvidyasagar]

yes, it loads just fine.

[sunny_talwar]

I don't see any issue with what you have shared... is there more to it then shared? Can you may be share the logfile to see what else might be going on?

[poluvidyasagar]

Individual tables loaded correctly before and now they are not loading.

I have attached test qlikview file. can you please help me figure this out.

[sunny_talwar]

I didn't see this earlier, but you are doing Max(Year) in your where clause... can you explain what exactly are you trying to do?

[sunny_talwar · Accepted solution]

May be you just want this?

LET WeekToday=Year(today()) & Num(Week(today()),'00');

Table1:
LOAD *
FROM [$(vL.QVDFolder)Table1.qvd](qvd)
Where Year(Date) & Num(Week(Date),'00') <> '$(WeekToday)';

Table2:
Load *
FROM [$(vL.QVDFolder)Table2.qvd](qvd)
Where Year(Date) & Num(Week(Date),'00') <> '$(WeekToday)';

[poluvidyasagar]

So i am trying to do incremental load weekly.

Weektoday:  Year(today()) & Num(Week(today()),'00') (in this example: say 202009 )

Max week number from table1:  Year(Max(Date)) & Num(Week(Max(Date)),'00')

So, load the data from table1 where yearweek is not equal to current yearweek.

In this example, i have only one date so Year(Date) might work. But if i do incremental load, i need year(max(date))

[sunny_talwar]

From the qvd, you want to load all the rows which are not associated with the most recent period, right? I don't think you need to add Max()... you will do a row by row comparison and will exclude anything which is not current period related... while loading everything else.

[poluvidyasagar]

Year(Date) will work only if i have one date. But i have more dates because of incremental load. This is why i am using  year(max(date))