Select first entry found from a table and ignore the rest

[robert99]

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)

[swuehl]

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

[robert99]

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

[robert99]

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

The distinct qualifier

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.

Example1:

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.

Example2:

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.

[robert99]

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;

[swuehl · Accepted solution]

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

[robert99]

swuehl

it works 

I very much appeciate your help on this. I would have never worked this out.

Thanks RJ