where match([%Sales Rep Key], fieldvalue('SalesRepIDSandip',RecNo()) ); does not loaded revious loaded records

[ananyaghosh]

Hi,

I am using the below code:

[TmpSalesRep]:
load
[%Sales Rep Key],
[%Sales Rep Key]  as Key1
resident [Temp Table]
where
"%Posting Date Key" = '20170307' and "Sales Posting Amount USD">0;

[TmpSalesRep3]:
Load num(fieldvalue('Key1',recno())) as SalesRepIDSandip,
recno() as RecNo
AUTOGENERATE fieldvaluecount('Key1');

[Final]:
Load
"%Sales Posting Day Key",
[Sales Posting Amount USD] as SalesAmounts,
"%Posting Date Key" as DateKey,
[Process Date] as PDate,
[%Sales Rep Key] as Key2
resident [Temp Table]
where match([%Sales Rep Key], fieldvalue('SalesRepIDSandip',RecNo()) );

Now where match([%Sales Rep Key], fieldvalue('SalesRepIDSandip',RecNo()) ); does not loaded previously loaded ([%Sales Rep Key] from [TmpSalesRep3] table.

Actually I want to load all the values from Key1 column at [TmpSalesRep] table and load those Key1 values at 'Final' table that it will satisfy the condition used in 'Final' table. So I have used [TmpSalesRep3] table to get all the Key1 values and use it in 'Final' table. But where match([%Sales Rep Key], fieldvalue('SalesRepIDSandip',RecNo()) ); does not working.

[swuehl]

Fieldvalue(), FieldValueCount() and also Exists(FIELD) does not query a resident table, like [TmpSalesRep3] table, but the field's symbol table:

Symbol Tables and Bit-Stuffed Pointers

I have no idea what you are trying to achieve, so it would be really helpful if you can start with a  short description of your model and modelling task (i.e. what you are trying to do).

[Kushal_Chawda]

try this

[TmpSalesRep]:
load
[%Sales Rep Key]  as Key1
resident [Temp Table]
where
"%Posting Date Key" = '20170307' and "Sales Posting Amount USD">0;

[Final]:
Load
"%Sales Posting Day Key",
[Sales Posting Amount USD] as SalesAmounts,
"%Posting Date Key" as DateKey,
[Process Date] as PDate,
[%Sales Rep Key] as Key2
resident [Temp Table]
where exists(Key1,[%Sales Rep Key]);

[ananyaghosh]

Hi,

I have explained my requirements clearly. Can you help me how to solve this issue?

[ananyaghosh]

HI,

you solution where exists(Key1,[%Sales Rep Key]); will not work, because [%Sales Rep Key] is renamed to Key2 in final table. So give me another idea.

[Kushal_Chawda · Accepted solution]

Before coming to any conclusion you should try it first. I don't see any reason that it will not work. Though you have renamed the field as Key2, first where condition executes before loading Final table, so you cannot write where exists(Key1,Key2), it will throw an error "Field not found"

[ananyaghosh]

ok. I am sorry for the comment and it is working for me.

Thanks,

Sandip

[Kushal_Chawda]

No problem bro.