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

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

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

swuehl — Sun, 21 May 2017 11:22:08 GMT

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).

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

Kushal_Chawda — Sun, 21 May 2017 11:42:58 GMT

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]);

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

ananyaghosh — Sun, 21 May 2017 16:10:29 GMT

Hi,

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

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

ananyaghosh — Sun, 21 May 2017 16:12:02 GMT

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.

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

Kushal_Chawda — Sun, 21 May 2017 16:27:33 GMT

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"

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

ananyaghosh — Sun, 21 May 2017 16:46:05 GMT

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

Thanks,

Sandip

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

Kushal_Chawda — Mon, 22 May 2017 04:13:26 GMT

No problem bro.