fieldvalue and fieldvalue count does not working

[ananyaghosh]

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

[trdandamudi]

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

[sunny_talwar]

Try this

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

Based on this thread

Multiple Question mark in list box

[ananyaghosh]

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?

[swuehl]

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

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

[sunny_talwar · Accepted solution]

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

[ananyaghosh]

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

[sunny_talwar]

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?

[ananyaghosh]

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?

[sunny_talwar]

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