I have the follwoing quey and the where function is not working.. I am sure bout the the values inside the columns but not sure why it is not working..
and I am trying to filter on the top table..
I appreciate any thoughts...
Thxs
MostRecentPaymenttemp:
LOAD
[LeaseAbstractID] AS [IHS_VP_RentScheduleCharges Lease ID],
DueBy,
([GL Amount] * ExchangeRate) AS [IHS_VP_RentScheduleCharges GL Amount],
[GL Account Name]
FROM
(txt, codepage is 1252, embedded labels, delimiter is '|', msq)
Can't see an error right now in your code (but maybe missing something).
It would be helpful if you could tell us what you mean with 'function is not working'. Error message, no records, all records, wrong records returned...
If you want to filter out all Account Names containing Base Rent, you could try
you'll need to give us a little more information what you are expecting to get and what you actually get.
Also some sample lines of data or a small sample qvw might help us to help you.
If you are unsure what part of your script might not work as expected, start from the first load, use an exit script statement or comment out all other lines and check what is returned from that load using the table view or a table box in the frontend. Then, proceed to the next load.
What i am trying to do is to get the Most recent payment and then create a new column to calculate Monthly payment which is equal to (Most recent payment * Annual Accurancy).
the first table loads the data from an external file.
The second one takes the fields and show the Most Recent value.
The third table in the qvw is the same as the first one but i am loading additional fields after droping the the first table.
In your second load, you are using a group by clause. So if you want to calculate
(IHSAnnualAccurancy) * LastValue([IHS_VP_RentScheduleCharges GL Amount]) ) As MonthlyPayment
this will not work unless you add IHSAnnualAccurance to your group by fields or unless you use an aggregate functions on this field (only, max, min, lastvalue .. whatever appropriate).
Regarding filtering by GL Account Name, you are building your aggregates based on [IHS_VP_RentScheduleCharges Lease ID], and there seems to be a 1:n relation between Account Name and Lease ID.
So I think you first need to rethink about the field you want to base your aggregates on (using the group by load). This field should probably be a key.
I tried to do it but I am having a little issues..
on the filtering. yes there is a 1:n between ID and Account Name..
the reason I want to filter by Account Name is I will have 1:1 relationship between the two fields.
On the new ield i am trying to calculate I added the Annual Accurancy on the second table and added that to the groupby statement but I am still getting error.
If you encounter an error after changing the script, you'll need to tell us the actual code of your script and the error you get (or post another sample file, but best with some input files or INLINE tables so we can actually do a reload).
I noticed also that your relationship between Account Name and Lease ID is even n:m so I am absolutely unsure how you want to filter on Account Name after first filtering some Account Names in your first load, then group by Lease ID and then link your Lease ID to a full table containing your Account Names and Lease IDs. It seems I don't understand your data model and requirements yet.
There is too much guessing right now here on my side, so I leave it to someone who might have a better understanding.
