Expression Help

[sona_sa]

Hi Friends,

I have Some no. of raw data, Now I have to make an expression Like - In Others Row - The Value will substract the Value from Top 50 Row.

I have given the Input and Output in attached xls. Please check the xls and help me to make an expression for the same.

Thanks.

[sona_sa]

No Solution or Any Doubts.

[NitinK7]

try below code

ABC:
LOAD
Category,
ID,
"Call",
"%"
FROM [lib://Desktop/DATA.xls]
(biff, embedded labels, header is 1 lines, table is Sheet1$);

temp1:
Load
Category ,
ID
Resident ABC where MixMatch(Category,'Others') ;
Let v_Others=Peek('ID',0,'temp1');
Drop Table temp1;

temp2:
Load
Category ,
ID
Resident ABC where MixMatch(Category,'Top 50') ;
Let v_Top50=Peek('ID',0,'temp2');
Drop Table temp2;

NoConcatenate

Final:
Load
Category,
if(MixMatch(Category,'Others'),('$(v_Others)'-'$(v_Top50)'),ID) as ID,
ID as ID1,
"Call",
"%"

Resident ABC;
Drop Table ABC;

[sona_sa · Accepted solution]

Thanks for the same. Let me try the same.

[sona_sa]

Hi Nitin,

Can you check my code and let me know, Where my code is wrong ?

I am not able to do the same.

Thanks.

[NitinK7]

if possible then can you please send me using .qvf file,

I dont have license version of qvw so unable to open your .qvw file

[NitinK7]

check once how many records are there in Stock_Temp1 and Stock_Temp2  table highlighted in green (should be only one)

Stock_Temp1:
LOAD
Category,
Cases
Resident Stock where MixMatch(Category,'RESIDENT INDIVIDUALS') ;
Let v_Others=Peek('Cases',0,'Stock_Temp1');
Drop Table Stock_Temp1;

Stock_Temp2:
LOAD
Category,
Cases
Resident Stock where MixMatch(Category,'PUB') ;
Let v_PUB=Peek('Cases',0,'Stock_Temp2');
Drop Table Stock_Temp2;

[sona_sa]

Hi Nitin,

Stock_Temp1 - 5 Rows

Stock_Temp2 - 250 Rows

Stock_Final - 34041 Rows.

I am not getting variable = v_PUB value, v_Others Value is coming properly.

Please check attached QVW. Same code I am using here.

Thanks 

[NitinK7]

only looking the script its very difficult to find oud solution

it would be great if you provide Stock table data in excel With masking/removing sensitive data

[sona_sa]

Hi Nitin,

PFA,