topic Re: fieldvalue and fieldvalue count does not working in QlikView

fieldvalue and fieldvalue count does not working

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

Hi,

I a using the below script:

[TmpSalesRep]:
load
"%Sales Posting Day Key",
[Sales Posting Amount USD] as SalesAmount,
[%Sales Rep Key] as AsOfSalesRepID
resident [Temp Table]
where
"%Posting Date Key" = '20170307' and "Sales Posting Amount USD">0;
Now the above table has 1307 records, but the below table generate only 607 records with all rows have '?' value.
[TmpSalesRep3]:
Load fieldvalue('AsOfSalesRepID',recno()) as SalesRepIDTmp
AUTOGENERATE fieldvaluecount('AsOfSalesRepID');

Please help me

Re: fieldvalue and fieldvalue count does not working

trdandamudi — Sat, 20 May 2017 11:10:05 GMT

I think it is because of the multiple AsOfSalesRepID. Remember FieldValue() and FieldValueCount() will only work with distinct field values. Because you are having multiple Rep IDs yiou are facing that issue.

Hope this helps...

Re: fieldvalue and fieldvalue count does not working

sunny_talwar — Sat, 20 May 2017 11:13:14 GMT

Try this

[TmpSalesRep3]:
Load Text(FieldValue('AsOfSalesRepID', RecNo())) as SalesRepIDTmp
AutoGenerate FieldValueCount('AsOfSalesRepID');

Based on this thread

Multiple Question mark in list box

Re: fieldvalue and fieldvalue count does not working

ananyaghosh — Sat, 20 May 2017 12:42:16 GMT

Ok,

Can you help me on the below script:

[Final]:
load
"%Sales Posting Day Key",
[Sales Posting Amount USD] as SalesAmounts,
[%Sales Rep Key] as Key

//,num(fieldvalue('SalesRepIDSandip',recno())) as Key2
resident [Temp Table]
where Exists(ID,Key)

here where exists clause cannot find 'Key' column. Any idea how to use that?

Re: fieldvalue and fieldvalue count does not working

swuehl — Sat, 20 May 2017 12:43:46 GMT

Key is an alias for [%Sales Rep Key], hence

WHERE EXISTS(ID, [%Sales Rep Key]);

Re: fieldvalue and fieldvalue count does not working

sunny_talwar — Sat, 20 May 2017 14:14:40 GMT

Stefan is right here, you can only use a field which is available in your load... if you are renaming it to something else it will only be available after the particular load has completed... so in your case you have 2 options

1) As Stefan mentioned

[Final]:
LOAD "%Sales Posting Day Key",
[Sales Posting Amount USD] as SalesAmounts,
[%Sales Rep Key] as Key
resident [Temp Table]
where Exists(ID, [%Sales Rep Key]);

2) Do this in a preceding load or resident load

[Final]:

LOAD *

where Exists(ID, Key);

LOAD "%Sales Posting Day Key",
[Sales Posting Amount USD] as SalesAmounts,
[%Sales Rep Key] as Key
resident [Temp Table];

I took the Where Exists to preceding load making it possible for me to use the new renamed field (Key) to be used in the Where Exists function

Re: fieldvalue and fieldvalue count does not working

ananyaghosh — Sun, 21 May 2017 08:28:56 GMT

Hi,

I have used your suggestion and using the below code:

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

[Final]:
load *
where Exists(Key1,Key2) /*and
//if(Key1=Key2,'Same ID', 'Different ID') as 'FlagID'*/;
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
"%Posting Date Key" <= '$(vPrevYearEndDate)' and
"%Posting Date Key" >= '$(vPrevEightYearStartDate)' and
[Sales Posting Amount USD]=0;

So Can I add one more conditions or flags in the preceding load with where exists clause like this:

load *
where Exists(Key1,Key2) and
if(Key1=Key2,'Same ID', 'Different ID') as 'FlagID'

and if I am adding one more condition it gives me error.

Can u give me the correct syntax for it?

Thanks,

Sandip

Re: fieldvalue and fieldvalue count does not working

sunny_talwar — Sun, 21 May 2017 11:22:07 GMT

You want to add more conditions? What is that condition If(Key1 = Key2,....)? Although, Key1 doesn't even exists in your table load, even if it did what would be the use of this because you are only bringing Key2 which exists in Key1 field... so when would you expect key1 to not equal to key2?

Re: fieldvalue and fieldvalue count does not working

ananyaghosh — Sun, 21 May 2017 16:17:30 GMT

Hi,

I have used the following code:

[Final]:

load *

where Exists(Key1,Key2);

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

"%Posting Date Key" <= '$(vPrevYearEndDate)' and

"%Posting Date Key" >= '$(vPrevEightYearStartDate)' and

[Sales Posting Amount USD]=0;

but want to use all the condition in the preceding load, so is there any option to do that?

Re: fieldvalue and fieldvalue count does not working

sunny_talwar — Sun, 21 May 2017 16:22:23 GMT

You mean like this?

[Final]:

LOAD *

Where Exists(Key1,Key2) and DateKey <= '$(vPrevYearEndDate)' and DateKey >= '$(vPrevEightYearStartDate)' and SalesAmounts = 0;

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

Re: fieldvalue and fieldvalue count does not working

ananyaghosh — Sun, 21 May 2017 16:33:40 GMT

yes

Re: fieldvalue and fieldvalue count does not working

sunny_talwar — Sun, 21 May 2017 16:46:02 GMT

Cool , I am glad it worked out for you