topic Re: Loading error with same column name from QVD using Where Clause in QlikView

Loading error with same column name from QVD using Where Clause

poluvidyasagar — Sat, 16 Nov 2024 01:09:35 GMT

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

Re: Loading error with same column name from QVD using Where Clause

sunny_talwar — Fri, 28 Feb 2020 16:22:03 GMT

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

Re: Loading error with same column name from QVD using Where Clause

poluvidyasagar — Fri, 28 Feb 2020 16:35:04 GMT

yes, it loads just fine.

Re: Loading error with same column name from QVD using Where Clause

sunny_talwar — Fri, 28 Feb 2020 16:37:22 GMT

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?

Re: Loading error with same column name from QVD using Where Clause

poluvidyasagar — Fri, 28 Feb 2020 17:20:05 GMT

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.

Re: Loading error with same column name from QVD using Where Clause

sunny_talwar — Fri, 28 Feb 2020 17:22:22 GMT

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?

Re: Loading error with same column name from QVD using Where Clause

sunny_talwar — Fri, 28 Feb 2020 17:27:12 GMT

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)';

Re: Loading error with same column name from QVD using Where Clause

poluvidyasagar — Fri, 28 Feb 2020 17:30:26 GMT

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))

Re: Loading error with same column name from QVD using Where Clause

sunny_talwar — Fri, 28 Feb 2020 17:36:51 GMT

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.

Re: Loading error with same column name from QVD using Where Clause

poluvidyasagar — Fri, 28 Feb 2020 17:38:20 GMT

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))

Re: Loading error with same column name from QVD using Where Clause

poluvidyasagar — Fri, 28 Feb 2020 17:43:59 GMT

yes you are right..I do not need Max since it is a row by row comparison.

Thank you

Re: Loading error with same column name from QVD using Where Clause

Vegar — Fri, 28 Feb 2020 17:47:49 GMT

Yes Year(Date) will only work if you have one value. In a chart object you will normally use max() because you are comparing multiple values, but when loading a qvd you are comparing one row at the time. So when reading a qvd without using GROUP BY you should use Year(Date) not year(max(Date)).