Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sona_sa
Creator II
Creator II

Expression Help

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.

Labels (2)
1 Solution

Accepted Solutions
sona_sa
Creator II
Creator II
Author

Thanks for the same. Let me try the same.

View solution in original post

16 Replies
sona_sa
Creator II
Creator II
Author

No Solution or Any Doubts.

NitinK7
Specialist
Specialist

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;

NitinK7_0-1638527768573.png

 

sona_sa
Creator II
Creator II
Author

Thanks for the same. Let me try the same.

sona_sa
Creator II
Creator II
Author

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

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

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
Creator II
Creator II
Author

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

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
Creator II
Creator II
Author

Hi Nitin,

PFA,