topic problem with if function in QlikView

problem with if function

Fri, 26 Aug 2011 09:13:38 GMT

Hi, I'am new to QLikView and do not have much in common with coding.. Could anyone help me with the case:

I have data loaded from two Excel sheets (let's say 'Excel1' and 'Excel2'). Then I would like to create classification based on comparison of dates (each one from different Excel). The names are: ‘Rev. rec. date’ and ‘Date (Installation)’

Under loaded fields in Excel2 I wrote:

// classification

If ([Rev. rec. date]) <= [Date (Installation)], ‘correct’, ‘incorrect’) as Classification

However, during load I receive comment that field [Rev. rec. date] was not found and I assume that it may be because it comes from different load of data. How should I indicate in this function that I refer to Excel1?

Thanks in advance for any tip.

problem with if function

Fri, 26 Aug 2011 09:32:54 GMT

Hi justynak,

Yes, you have to concate the two tables.

For example:

Table1:

LOAD

Date1,

Date2,

Rev. rec. date

FROM Excel1;

JOIN LOAD

Date1,

Date3,

Date (Insatallation)

FROM Excel2;

Then you have a table with the fields Date1, Date2, Date3, Rev. rec. date, Date (Installation) and then you could do you if-statement. The joining of two tables based on one field, which is equal in each excel-sheets.

Table2:

LOAD

Date1,

Date2,

Date3,

if([Rev. rec. date] <= [Date (Installation)], 'correct', 'incorrect') AS Classification

RESIDENT Table1;

DROP TABLE Table1; //otherwise you have to rename the fields in Table2 with AS

Regards

vicky

problem with if function

Mon, 10 Oct 2011 20:52:55 GMT

Hi vicky, I'm reading this thread and I have a question about your use of Resident in conjunction with the joins above as I've seen this elsewhere and I'm wondering if this might solve a problem I'm having.

In the case above, why couldn't justynak put the IF statement right in the table 1 load like:

Table1:

LOAD

Date1,

Date2,

if([Rev. rec. date] <= [Date (Installation)], 'correct', 'incorrect') AS Classification

FROM Excel1;

Does that cause some kind of problem in Qlikview? I am basically doing that in my script but I'm seeing weird things happen like tables that won't join properly. I'm wondering if it's some limitation in the software.

Thanks

problem with if function

Tue, 11 Oct 2011 08:15:47 GMT

Hello mhamberg,

The problem is, that the field Date (Installation) is not a field in the first Excel-document, so you get the Error 'Field not found'.

That's why I have propose to connect this to tables in one table. Then the if-statement will work.

Joining tables works fine, if the tables have one or more field names eqal. Therefor John has giving a quite good post: http://community.qlik.com/thread/30318

regards vicky

problem with if function

Tue, 11 Oct 2011 09:07:15 GMT

hi justynak,

you can also do like this:

1.load data from both the excel sheets.

2.make qvd's of both the tables:

eg: store tablename into qvdname.qvd;

3.fetch data from both qvd's under one table by join.

4. then you can compare the two fields from different tables.

hope this will help you.

problem with if function

vincent_ardiet — Tue, 11 Oct 2011 09:15:31 GMT

Hi Vicky,

Have you try to use the Peek or Lookup functions ?

We don't know the associative link between your 2 tables but with those functions you will be able to read a value from another table without joining it.

Regards,

Vincent

problem with if function

Tue, 11 Oct 2011 11:02:59 GMT

u can fetch the value in variable using peek function:

let v = peek('Rev rec. date', 'rowno', 'tablename');

for rowno you can use variable.

by doing this you get the value of rec. date in a variable and then you can use this variable in your expression.

Re: problem with if function

Tue, 11 Oct 2011 11:17:41 GMT

hi vijit, hi vincent,

yes, meanwhile I know this functions

The proposal is from august, there I was glad to see the result I want to have before. Today I only explain, why I choose that way.

And there are always different ways

regards

vicky