problem with if function

[#1]

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.

[#2]

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

[#3]

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

[#4]

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

[#5]

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.

[vincent_ardiet]

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

[#7]

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.

[#8]

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