topic Re: need resident load help in QlikView

need resident load help

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

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.

Re: need resident load help

kaushiknsolanki — Wed, 03 May 2017 08:34:49 GMT

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

Re: need resident load help

Wed, 03 May 2017 09:27:11 GMT

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?

Re: need resident load help

Anil_Babu_Samineni — Wed, 03 May 2017 09:31:59 GMT

Can you share that sql query ??

Re: need resident load help

Wed, 03 May 2017 09:40:05 GMT

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;

);

Re: need resident load help

Anil_Babu_Samineni — Wed, 03 May 2017 09:47:36 GMT

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;

Re: need resident load help

Wed, 03 May 2017 10:16:33 GMT

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.

Re: need resident load help

Anil_Babu_Samineni — Wed, 03 May 2017 10:20:21 GMT

You should use Left join (Fact Table Directory)

Re: need resident load help

Wed, 03 May 2017 10:23:52 GMT

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?

Re: need resident load help

sasikanth — Wed, 03 May 2017 10:49:11 GMT

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

Re: need resident load help

Anil_Babu_Samineni — Wed, 03 May 2017 12:49:54 GMT

Seems, you are using Left join for resident table only. Unless your expression is okay to me. Can you describe more on Variables?