Actual V/S Budget Issue in report

[Aspiring_Developer]

Hi Team, I am new to Qlik and having a doubt on a project which i need to deliver asap. Please help!!

My data set as below:

Actual:
LOAD
object_no,
Ledgerno_LEDNR.RPSCO ,
Valuetype_WRTTP.RPSCO ,
FiscalYear_GJAHR.RPSCO,
DebitInfo_BELTP.RPSCO,
Flag ,
months,
values,
New_Month,
"Date"
FROM [lib://QVD_ICON (varpwqlkdev1_visguest)/Actual.qvd]
(qvd) ;

left join(Actual)

Budget:
LOAD
"Date",
OBJNR as object_no,
LEDNR as Ledgerno_LEDNR.RPSCO,
WRTTP as Valuetype_WRTTP.RPSCO,
'Budget'as Flag ,
GJAHR as FiscalYear_GJAHR.RPSCO,
Budget
FROM [lib://QVD_ICON (varpwqlkdev1_visguest)/12febBudget.qvd]
(qvd);

Now i want to fetch the data for actuals and budget on the basis of Object Id. I tried using left join . When take the data in tablebox i get the correct values for Actual and Budget. However, when i take the data in pivot and apply sum on values and budget either it gives me incorrect numbers and when i try using flag in expressions (sum({<Flag=Áctual>}Values)', it straightaway give the zero values . 

Below is my table box output

objectNo| values | Budget| ValueType|Sum(Budget)|Sum(values)|

PR00000007| 4736| 3689.333|04|450098.66| 5335792

These values like 4736 and 3689 are displayed with respect to fiscal year 2013 and these id's only have the data in the month of April. So even if we sum the values for one year i.e 2013 it should give only 4736 as it does have  values in other months.

I have attached  my table box output screenshot for a better understanding:-

Request everyone to please help me as i am struggling on it for a  long time !!!

[sumanta1234]

Hi,

when you are loading data to join please check no of records of data. Is it increasing or not when you are joining.

For that put Recno() as field in the table.

[marcus_sommer]

Quite often the join of actual/budget datasets isn't possible because usually they don't contain exactly the identically keys - this means the join may increase the number of records by a 1:n or n:n relationship and even more likely it would reduce the number of records by a 1:1 relation if there are missing keys.

Beside the efforts to create a link-table between them or to prepare both datasets before joining them there is a much simpler solution just by concatenating them.

- Marcus

[Aspiring_Developer]

Hi Marcus,

Thank for your response.

I have tried concatenating two tables which gives the correct values for Actuals but for Budget the data is getting double .

Please suggest

[marcus_sommer]

It shouldn't happens. It looked that your data didn't contain your expected values. This could be checked by adding recno() and rowno() to your load and then using a tablebox with them and your fields.

- Marcus

[Aspiring_Developer]

Hi Marcus,

I have checked and the rowno() and recno() has the same set of values .

Please suggest

[marcus_sommer]

It wasn't meant to compare recno() and rowno() against eachother else to ensure with them to display all records within the tablebox because Qlik shows only distinct records respectively combinations of dimension-values so that any duplication of the data wouldn't be visible without an unique key.

- Marcus