topic Re: Expression Help in QlikView

Expression Help

sona_sa — Thu, 02 Dec 2021 13:32:22 GMT

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.

Re: Expression Help

sona_sa — Fri, 03 Dec 2021 04:47:03 GMT

No Solution or Any Doubts.

Re: Expression Help

NitinK7 — Fri, 03 Dec 2021 10:36:28 GMT

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;

Re: Expression Help

sona_sa — Fri, 03 Dec 2021 12:12:58 GMT

Thanks for the same. Let me try the same.

Re: Expression Help

sona_sa — Wed, 08 Dec 2021 08:08:35 GMT

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.

Re: Expression Help

NitinK7 — Wed, 08 Dec 2021 08:55:43 GMT

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

Re: Expression Help

NitinK7 — Wed, 08 Dec 2021 09:22:40 GMT

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;

Re: Expression Help

sona_sa — Wed, 08 Dec 2021 09:36:19 GMT

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

Re: Expression Help

NitinK7 — Wed, 08 Dec 2021 12:05:50 GMT

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

Re: Expression Help

sona_sa — Wed, 08 Dec 2021 13:04:53 GMT

Hi Nitin,

PFA,

Re: Expression Help

NitinK7 — Wed, 08 Dec 2021 13:25:22 GMT

if you can see below screenshot there are multiple

RESIDENT INDIVIDUALS and PUB then which specific record do you wnat to perform your minus operation

Re: Expression Help

sona_sa — Thu, 09 Dec 2021 06:06:04 GMT

Yes, I want the same.

Thanks

Re: Expression Help

NitinK7 — Thu, 09 Dec 2021 08:16:46 GMT

if you wnat the same ,then can you please let me know which output do you want

based on your given STOCK.excel file?

when we write the below code then getting only first record from the table.

Let v_Others=Peek('Cases',0,'Stock_Temp1');

Re: Expression Help

sona_sa — Thu, 09 Dec 2021 10:03:54 GMT

But I have to substract the numbers from Residents Individuals - PUB. That how I will do the same.

Re: Expression Help

NitinK7 — Thu, 09 Dec 2021 10:50:38 GMT

you are right,

I just wnat to know what output /result do you want, because if you can see there are 2 different values/Cases for RESIDENT INDIVIDUALS and there and multiple PUB with null values/Cases.

so which one of the following is true

1. RESIDENT INDIVIDUALS (133526) - PUB (0 ) -------// case value define in bracket

2. RESIDENT INDIVIDUALS (136056) - PUB (0 ) -------// case value define in bracket

Re: Expression Help

sona_sa — Thu, 09 Dec 2021 11:09:38 GMT

Hi Nitin,

It is OK, we will see the data week wise, So there is no problem, If we do the same for first record of data, But I have to do substract from v_Others - v_PUB.

I need output like - 136057 - 50 = 136007

Thanks

Re: Expression Help

sona_sa — Thu, 09 Dec 2021 11:19:26 GMT

I know -

But Instead of PUB(0), It should be PUB(50).

Because after this logic same logic I will use for next expression. for Shares. for amount.

😂.