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)
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
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
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
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?
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];
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
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
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
Script error after changing the above
Invalid expression
left join (TableA) LOAD
[SerialNo],
Date(min([serialDate])) as minDate
resident TableB group by [serialDate]
i just tried distinct
But this also did not work