Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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.
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
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.
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