need resident load help

[ananyaghosh]

Hi,

I need to load SalesREP ID whose current sales value for the current month is >0, but for last 8 years's sales value will be 0.

So I have written the below code in script:

[TmpSalesRep]:

first 2

load 

[%Sales Trn Key],

[Process Date] as DateKey,

[Sales Amount USD] as SalesAmount,

[Sales Rep ID] as AsOfSalesRepID

resident [TmpTable]

  where

   [Process Month] = 'March' and [Sales Amount USD]>0;

left join

load

[%Sales Trn Key],

[Process Date] as DateKey,

[Sales Amount USD] as SalesAmount,

[Sales Rep ID] as AsOfSalesRepID

resident [TmpTable]

where

  [Process Date] >= '$(vPrevEightYearStartDate)' and

    [Process Date] <= '$(vPrevYearEndDate)' and

    [Sales Amount USD]>=0;

  //$(vCurrentMonth)

drop table [TmpTable];

Please let me know if I am wrong because I am getting wrong data.

[kaushiknsolanki]

Hi,

Instead of left join use concatenate.

Also please do not create multiple same post with different users.

Need resident load help

Regards,

Kaushik Solanki

[#3]

hi,

your solutions does not correct the value that I am getting.

Can I add this type of query in resident load like:

where match(SALESRepID, <sql query>);

So it will do a subquery same as SQL?

[Anil_Babu_Samineni]

Can you share that sql query ??

[#5]

select SalesRepID from table where  [Process Month] = 'March' and [Sales Amount USD]>0 and

SalesREpID in(

[Process Date] >= '$(vPrevEightYearStartDate)' and

    [Process Date] <= '$(vPrevYearEndDate)' and

    [Sales Amount USD]=0;

);

[Anil_Babu_Samineni]

May be this?

Load SalesRepID from Table where (Match([Process Month]='March') and [Sales Amount USD] > 0) OR Match([SalesREpID], [Process Date] >= '$(vPrevEightYearStartDate)' and [Process Date] <= '$(vPrevYearEndDate)' and [Sales Amount USD] =0;

[#7]

How the last one work:

Match([Sales Rep ID],[Process Date] >= '$(vPrevEightYearStartDate)' and

    [Process Date] <= '$(vPrevYearEndDate)' and [Sales Amount USD] =0)

? as it will not get any Sales Rep Id through a load statement.

[Anil_Babu_Samineni]

You should use Left join (Fact Table Directory)

[#9]

hi,

I have used the left join and you can see my main post here. But it is collecting the rep id from first result set and also taking the rep id which is in second result set. But my  requirement is it will satisfy the first conditionand also the second condition means:

collect the rep id which has sales for current month, but does not have any sales for previous 8 years .

So what can I do now?

[sasikanth]

hi

Try this

Load

[%Sales Trn Key],

[Process Date] as DateKey,

[Sales Amount USD] as SalesAmount,

[Sales Rep ID] as AsOfSalesRepID

resident [TmpTable]

  where

  [Process Month] = 'March' and [Sales Amount USD]>0;

left join

LOAD AsOfSalesRepID  where SALES_AMT='0';

LOAD

[Sales Rep ID] as AsOfSalesRepID,

SUM([Sales Amount USD]) as SALES_AMT

Resident [TmpTable]

Where

[Process Date] >= '$(vPrevEightYearStartDate)' and

[Process Date] <= '$(vPrevYearEndDate)' ;