How to compare data between certain tables

Mon, 20 Jun 2011 11:30:42 GMT

We have to make dashboard for a production site

There is a table called ld_det which is having the details for different locations say for eg
at location pitsburg 50 nut bolts(spare part) were sold on 12th June 2011"

Now there is another table tr_hist which is having details of each transaction
like on 12th June 2011 following transaction was done for nut bolt

10 at 12:00pm for site pitsburg
10 at 1:00 pm for site pitsburg
....
so on.

Now we are genrating a report that will show elapsed time

what is elapsed time?

Suppose if order has been taken by production site and spare part is not available at that particular but they know it will be available before the delivery date.
so the time and date of that particular transaction is elapsed time.

for example there were 50 nut bolts available on production site and order was taken for 70 nut bolts

so the time of transaction when it crossed 50 is called elapsed time

so considering the above example
suppose in ld_det on 12th june 2011 for pitsburg production site there were 5 coresponding rows in tr_hist.

So I will write the query in SQl as following

select l.ld_det_name, l.ld_det_spare, l.ld_det_date, elapsed_time(l.ld_date) from ld_det l inner join

tr_hist t on l.ld_date = t.tr_date where elapsed_time(l.ld_date) <> 0
where elapsed_time can be a subroutine/function/procedure
which will calculate based on following logic

x number;

for eacr row in ld_det

x = ld_tranc

for each row in tr_hist where tr_part = ld_part and tr_date = ld_date

if x > 0
x = x - tr_tran

if x < 0 the

return tr_time

exit

else
next row of tr_hist

end if

end loop1
end loop2

return 0
end fucntion.

Kindly suggest regarding the issue ASAP

thanks in advance!!!!!!!!

topic How to compare data between certain tables in QlikView

How to compare data between certain tables