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]

RJ,

I assume the correct Date purchase is the one with minimum date, right?

You could indeed use a left join, maybe like this:

TableA:

LOAD * INLINE [

Call, Serial number

1,123

2,124

];

TableB:

LOAD * INLINE [

Serial number, Date purchaseD

123,01/01/2003

123,01/02/2003

124,01/03/2003

];

left join (TableA) LOAD

[Serial number],

Date(min([Date purchaseD])) as minDate

resident TableB group by [Serial number];

So I grouped the TableB records by serial number and only retrieved the min date, then joined this one record to Table A.

Alternatively, you could use similar in the frontend, leaving TableA and TableB as read in raw:

If you created a table chart with Call and Serial number as dimensions, use something like

Date(aggr( min([Date purchaseD]),Call,[Serial number]))

as expression.

Hope this helps,

Stefan

[robert99]

Ive tried this with many different combinations (Ive spend many hours on this already)

Nothing worked

But thanks for the reply. Im surprised that qlikview does not have a simple solution for it as it surely is a common requirement. (It just a  left join but only to one record in another table)

It is very easy to do in excel so I can go this route

[swuehl]

I think above should work, at least it works here. Do you mind sharing what the problem is or which error you get returned?

I understand that you could do this also in excel, but I really believe that you could do this quite easily in QV, too.

Do you have more fields in table B you need to join to table A also?

[robert99]

TableA:LOAD * inline
     [FSR_Call_Num as Call_Num,
     FSR_Num,
     FSR_Ser_Num as SerialNo,
     FSR_Rep_Code,
     FSR_Fault_Code,
     FSR_Prod_Num
FROM
]
(ooxml, embedded labels, table is SCFSR);

TableB:
load * inline
     [Ser_Num as SerialNo,
     Ser_War_Start_Date as serialDate
From
]
(ooxml, embedded labels, table is SCSER);

left join (TableA) LOAD
[SerialNo],
Date(min([serialDate])) as minDate
resident Object group by [serialDate];

[swuehl]

HI RJ,

first, I think you need to remove the inline  from your loads, since you are loading from excel source.

inline is only needed if you define that data table within your script.

You should check that your both tables are loaded successfully prior trying to join them, e.g check your usage of the opening bracket [ in some of your excel field names, I would assume, that this might confuse QV, but I am not sure.

Maybe just use the assistent to create those two loads for you.

In your left join load, I think you need to do a resident TableB instead of resident Object.

Hope this helps,

Stefan

[robert99]

swuehl

Attached is the script above

I was doing most of the work in excel and was just joining table in qlikview. Then downloading back into excel when the join was done to do more work and comparisons

I was just seeing if I could do more in QlikView as the files are so big Excel is a bit slow

TBH I think for someone like me excel mostly with a small amount in qliview is probably the best option.  Qlikview is a brilliant product (esp compared to rubbish like Cognos) but the manuals and help is poor (most computer manual are though) and its is time consuming to do what I need to do. For example a simple join like above

[robert99]

Object is a mistake. I changed the table names quickly to fit with your instructions before coping and posting but forgot to change this name

[robert99]

Script error after changing the above

Invalid expression

left join (TableA) LOAD

[SerialNo],

Date(min([serialDate])) as minDate

resident TableB group by [serialDate]

[robert99]

i just tried distinct

But this also did not work