sales Vs budget

[#1]

Hi all,

     I am doing sales application in that i have given condition that item which is sold out (ie) Type= sale, so it will display only sold item. But when i compare this sale quantity with budget quantity for some other reason their is no entry for few budget item fell into value entry  table. And here my issues is in need to bring item from budget table which is not value entry (type not = sales). Kindly Help

ValueEntryNewOne:

Load    

    *,

        D_REGION&'-'&ItemNo&'-'&VPeriod as vvvv,

         D_REGION&'-'&ItemNo&'-'&VPeriod  as BudKey

From ValueEntry;

Budget:

Load

Region&'-'&ItemCode&'-'&Period as BudKey

From Budget;sales

[its_anandrjs]

Please explain with an example i am not clearly understood.

[preminqlik]

ValueEntryNewOne:

Load   

Region,

ItemCode,

Period,

Amount,

'Sales'             as               Type 

From ValueEntry;

concatenate

Load

Region,

ItemCode,

Period,

Amount,

'Budget'             as               Type           

From Budget;

-----> now in front end

Actual :

sum({<Type={'Sales'}>}Amount)

Budget

sum({<Type={'Budget'}>Amount}

[its_anandrjs]

If i understand clearly if you have budget data then just concatenate the budget data with the sales data like

Load your table like

ValueEntryNewOne:

Load   

    SalesValue as Amt,

    D_REGION&'-'&ItemNo&'-'&VPeriod as vvvv,

    D_REGION&'-'&ItemNo&'-'&VPeriod  as BudKey

    'Sales' as TableFlag

From ValueEntry;

Concatenate

Budget:

Load

BudgetAmount as Amt,

Region&'-'&ItemCode&'-'&Period as ww,

Region&'-'&ItemCode&'-'&Period as BudKey,

'Budget' as TableFlag

From Budget;

Regards

[#5]

hi Anand thanks for you reply.....

As of now the items which has  both value entry table and budget show perfectly ,but the issue is i need to show extra items from budget table that too in which value entry table does not have (ie) i need to display items which is not sale also.

[#6]

Dear Prem ,

Thanks for your reply .

I have tried with your concept,but i could not get exact result. Issue for me is i can show all the items which has an entry in value Entry table ,but i need to display items which not fall any entry in value entry table but budget table has that item .

As of now i got budget value as 1442619 but i must get 1450770 .This because one item is missing while i link this two table using key .so i need display that item .

1442619+8151=1450770

8151 is the item which is missing in value entry table (ie) the item which does not have any entry also should be displayed.

[its_anandrjs]

Suppose you want to load extra values from Budget table then in sales table give same name of the field name but leave it blank like a dummy field see the code for that

ValueEntryNewOne:

Load  

    SalesValue as Amt,

    D_REGION&'-'&ItemNo&'-'&VPeriod as vvvv,

    D_REGION&'-'&ItemNo&'-'&VPeriod  as BudKey

    'Sales' as TableFlag,

    '' as Extrafield1,// data is not available here and it is dummy field

    '' as Extrafield2 // data is not available here and it is dummy field

From ValueEntry;

concatenate

Budget:

Load

BudgetAmount as Amt,

Region&'-'&ItemCode&'-'&Period as ww,

Region&'-'&ItemCode&'-'&Period as BudKey,

'Budget' as TableFlag,

Extrafield1,//data is available only with budget table

Extrafield2 //data is available only with budget table

From Budget;

[bertdijks]

I think you don't have to add the 'dummy'fields in at the ValueEntryOne load.

Because you entered the concatenate keyword, thinks will work without the extra code:

Load   
Region,
ItemCode,
Period,
Amount,
Amount as SalesAmount,
'Sales'             as               Type 
From ValueEntry;

concatenate

Load
Region,
ItemCode,
Period,
Amount,
'Budget'  as               Type,
Amount as BudgetAmount,
extrafield1,
extrafield2
From Budget;

You see that I also added two new measures (SalesAmount an BudgetAmount in the script; I think you better use this, it is simplere for the frontend, and goves better performance.

[#9]

No i dont want to add any fields , let me explain with an example.

if value entry table has item xxx,yyy,zzz

and budget table has xxx,yyy,zzz,ccc

if i link these two table i get xxx,yyy,zzz but not ccc, i need to display this ccc item also if i sum(budgetquantity) it may show the value but in sum(salesquantity) it should displays  as zero

[arsal_90]

Can u give some sample data of both tables and what their output you want Because Prem do the right thing