Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I can do this is excel first (using vlookup) and then just load table A (with the table B date) into QlikView.
But I assume thsi can be easily done in QlikView but I can not see how
What I want to do is only select the first record from table B
So table A
Call ---Serial number
1 ---------123
2 ----------124
Table B
Serial No--- Date purchaseD
123 -----------1/01/2003
123 ------------1/02/2003 (entered in error)
124 -----------1/3/2003
Serial number 123 has been entered twice in error in table B. I only want one date not two in the combined table
Thanks for any help on this.
(I was hoping their would be something like a left join one record only)
Could you post a sample of your two Excel tables here?
I don't need the real data, just dummy data and your field names / table structure.
Regards
Stefan
Ive reduced this to just trying to load a simple excel file into QV without the duplicate SERial_NUMbers
The file structure
Some serial number appear twice which causes a doubling problem when linked to another file
I did try this using distinct but it makes no difference
Ser_Num | Ser_War_Start_Date |
439t03615 | 26/08/2011 00:00 |
00000 | 01/01/1980 00:00 |
0000000 | 11/07/1995 00:00 |
0000115 | 28/11/2005 00:00 |
0000116 | 28/11/2005 00:00 |
0000117 | 28/11/2005 00:00 |
0000119 | 28/11/2005 00:00 |
Go microsoft
They have a feature under data to remove duplicates. And it is easy to set up and works just as I want.
It took about 5 minutes to find this and to do the whole process
Excel and QV together seems like a brilliant combination. I was trying to do more in QV and some thing I have done but others the Excel route is still much better
nb The distinct qualifier should do this but it just does not work
If the distinct qualifier is used in the QlikView Load statement, duplicate records will not be loaded. However, the distinct qualifier is slightly different from that of a Select statement in SQL: In a Loadstatement, the distinct qualifier will be applied as the very last step of the statement execution. Which means that it can affect also other tables in the script.
Load A, B from Table1.txt ;
Load distinct A, B from Table2.txt;
Here, the two tables will first be concatenated and thereafter duplicate records of both input tables will be omitted.
Load A, B from Table1.txt ;
Join
Load distinct A, C from Table2.txt;
Here, the two tables will first be joined and thereafter duplicate records of the newly created table will be omitted.
This did not work
Alltable:
LOAD
text (Ser_Num) as serial,
date (Ser_War_Start_Date) as date
FROM
D:\QlikView\TESTQLIKVIEW.xlsx
(ooxml, embedded labels, table is SCSER);
TABLE2:
LOAD DISTINCT serial
RESIDENT Alltable;
Hi RJ,
RJ wrote:
Script error after changing the above
Invalid expression
left join (TableA) LOAD
[SerialNo],
Date(min([serialDate])) as minDate
resident TableB group by [serialDate]
I think it need to be like
left join (TableA) LOAD
[SerialNo],
Date(min([serialDate])) as minDate
resident TableB group by [SerialNo];
And could you post your two excel files here? With or without your current qvw file (with file would be better, of course)?
Regards,
Stefan
swuehl
it works
I very much appeciate your help on this. I would have never worked this out.
Thanks RJ