topic Re: if statement based on calculation in QlikView

if statement based on calculation

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a simple if statement which seem to be not working.

Im trying to create a new variable, which based on the results of 'Count' will assign a value to another one. If the count is 0, the value is D, otherwise it is A

I tried the following:

IF(COUNT([X_1])=' ','D','A') AS Var1

but it is not working. What is the correct way of doing this?

EDIT

to elaborate more on my question. I have 2 tables, which looks like this:

Table TD

CID	OID
1	x12
1	x23
1	x32
2	x41
2	x53
3	x62

The other table, looks like this:

Table CD

CID	CNAME
1	John Smith
2	Max Mustermann
3	Edith Bingo
4	Albert Reckel
5	Sam Retro
6	Teno

What i want to do, is to generate a new column for the 2nd table, which is based on the count of OID in the 1st table, i.e., this column should calculate, if count(OID) is NULL, the value should be D, else it should be A.

This is how my I try to do the left join at the moment:

left join (CD)

load

IF(COUNT([OID])=0,'Dormant','Active') AS Var1

Resident TD

And this new Var1 variable, will be used in a plot afterwards.

Does this make things more clear now?

Re: if statement based on calculation

Anil_Babu_Samineni — Wed, 18 Oct 2017 15:00:24 GMT

Try this?

If(COUNT(X_1)=0,'D','A') AS Var1

Re: if statement based on calculation

matteo_mi — Wed, 18 Oct 2017 15:08:10 GMT

Hi,

go to settings, variable overview

and create the new variable with your needs.

hope this help

Re: if statement based on calculation

madhumitha — Wed, 18 Oct 2017 15:58:10 GMT

Hello Abdullah,

You cannot assign values to variables with 'AS' key word. It should be either 'LET' or 'SET'. You can also use variable overview tab to create new variables.

Hope this helps

Re: if statement based on calculation

Thu, 19 Oct 2017 07:52:01 GMT

does not work.

Re: if statement based on calculation

Thu, 19 Oct 2017 08:00:37 GMT

Im not assigning a value to a variable, I want the value to be calculated in the script based on this condition. Then I want to use this value for plotting.

Re: if statement based on calculation

Thu, 19 Oct 2017 08:01:38 GMT

How can I then use this variable in a plot? will it adjust automatically to the dimension I use for the plot (e.g., the count for each ID should not be 0)?

Re: if statement based on calculation

matteo_mi — Thu, 19 Oct 2017 08:08:12 GMT

Hi Abdullah,

to use variable in this he script you should use SET or LET as explained above by Madhu Mitha.

Maybe what you are trying to accomplish have to be done in other way, so if you want you can

explain more in detail what is your goal posting an example.

best regards

Re: if statement based on calculation

marcus_sommer — Thu, 19 Oct 2017 08:20:17 GMT

It's not quite clear what you are trying to do. Do you want check if this field-value is empty, NULL or contained a white-space then you need a check on the lenght, for example len(X_1) = 1 or len(trim(X_1)) = 0 or do you want to count the fieldvalues and check these amounts with something like >= AnyNumber. To count fieldvalues within a load meant you need to use an aggregation load with group by.

- Marcus

Re: if statement based on calculation

Peter_Cammaert — Thu, 19 Oct 2017 09:02:56 GMT

If you want to do this in your load script, then follow these steps. We are assuming that X_1 is a column in a resident table called (for example) Table_1. If the table has a different name, then change the code accordingly.

Table_Count_X_1:

LOAD IF (Count(X_1)=0, 'D', 'A') AS Flag_X_1

RESIDENT Table_1; // Modify if resident table has a different name

LET Var1 = Peek('Flag_X_1'); // Var1 now contains either D or A

DROP Table Table_Count_X_1;

If you want to count the number of unique values in X_1, then add a DISTINCT keyword before X_1.

Best,

Peter

Re: if statement based on calculation

Thu, 19 Oct 2017 09:41:26 GMT

how can I then use Var1 for plotting? I cannot find it in the variable list

Re: if statement based on calculation

Thu, 19 Oct 2017 09:45:17 GMT

I updated my question. Maybe this explains more what I exactly need

Re: if statement based on calculation

Peter_Cammaert — Thu, 19 Oct 2017 09:51:00 GMT

Do not mix this in your JOIN. Because you want to load a variable (can contain one value in two formats, nothing more) you have to calculate the count outside of everything else.

Please do not change your requirement halfway in the discussion, as you will make an entire thread useless. Many people spend their time trying to help you, and time is never a free resource for them.

Re: if statement based on calculation

Thu, 19 Oct 2017 09:58:06 GMT

I wasnt clear from the beginning, im sorry, I updated my question and made it more clear now. How can this be doable with qlikview?

Re: if statement based on calculation

Peter_Cammaert — Thu, 19 Oct 2017 10:03:47 GMT

Your changed requirement has to be implemented in a different way. You are now asking for a Count() calculated per CID value. You can't use a variable for that, as a variable can only contain a single value (in two formats).

A JOIN like you propose brings an additional problem. CIDs that are not present in table TD will create a NULL value instead of value 'A'. So we have to resort to a trick that uses a mapping table and a call to ApplyMap. Something like this:

MapCID2Count:

MAPPING

LOAD DISTINCT CID, 'Active' AS Result

RESIDENT TD;

LEFT JOIN(CD)

LOAD DISTINCT CID,

ApplyMap('MapCID2Count', CID, 'Dormant') AS Var1

RESIDENT CD;

Best,

Peter

Re: if statement based on calculation

its_anandrjs — Thu, 19 Oct 2017 10:37:26 GMT

In this way, you can't use aggr function count in the load script you have to use group by though then

Load

IF( [X_1] = ' ' and Len([X_1])=0 and isnull([X_1]) = -1,'D','A') AS Var1

From ......

Re: if statement based on calculation

effinty2112 — Thu, 19 Oct 2017 11:04:26 GMT

Hi Abdullah,

Maybe:

Table1:

LOAD * INLINE [

CID, OID

1, x12

1, x23

1, x32

2, x41

2, x53

3, x62

];

MappingCount:

Mapping

LOAD

Distinct

CID,

'A'

Resident Table1;

Table2:

LOAD

ApplyMap('MappingCount',CID,'D') as NewDim;

LOAD * INLINE [

CID, CNAME

1, John Smith

2, Max Mustermann

3, Edith Bingo

4, Albert Reckel

5, Sam Retro

6, Teno

];

giving the Table2:

CID	CNAME	NewDim
1	John Smith	A
2	Max Mustermann	A
3	Edith Bingo	A
4	Albert Reckel	D
5	Sam Retro	D
6	Teno	D

Regards

Andrew