How to display the value of a variable with the help of select/load statement.

[#1]

Hello Everybody,

I am new to QlikView and facing some problem.

The scenario is : I have taken few records from a table and stored it's value in variables.

Then I have done operation on certain variables and calculated new values.

Now I want the new values corresponding to each record to be displayed either by select or load.

Just to Examplify :

Suppose I have a table say customer_info in my database.It contains 100 records.It's fields are Customer_id and Balance.

Now I am loading the data in qlikview with the help of SQL statement . and storing the records in two variable say cust_id and bal by using peek()[taking on row at a time].

Now peek is being used for each row to get the value in individual columns into corresponding variables.

As 1 row data isextracted in the variable, I am doing some operation on variable 'bal'. These operations are complicated , like I have to query in which catergory the customer is present .This category field is in a different table.There are some more conditons on values of different tables.Based upon these conditions I am modifying the 'bal' variable.

Now I want to display the Customer_id(which is stored in cust_id field) and the bal field (which I have modified).

After that I will run the same command for fetching the next record by using peek () with a variable say counter (counter value is from 0 to 100).

Thus, after this I will have a new table with customer_id and new balance.

So I wanted to know is it possible to dispaly a Qlikview variable in a load or select ? If so, how?

Kindly help.

Thanks in Advance !!!

[Miguel_Angel_Baeyens]

Hello,

I'm afraid I'm missing something here. If I got you right, you want to get your customers and their categories, right?

QlikView links two tables provided one field in both is named alike. So you can do something like the following, instead of looping (which is probably taking some time to execute):

Customers:
LOAD Customer_id,
     Balance,
     Customer_Category;
SQL SELECT Customer_id, Balance, Customer_Category FROM customer_info;
Categories:
LOAD Customer_Category,
     Category_Name;
SQL SELECT Customer_Category, Category_Name FROM categories_table;

If not, can you please provide with some sample data and what results you want to get?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Hi Miguel,

Thanks for swift reply!!

Sorry I was unable to make my point clear.

I am giving you the sample data :-

Customer_info table has records like :

Customer_ID          Balance

1                             10000

2                               3000

....

Now what I am required is to take balance corresponding to each row and calculate new balance.

Suppose the customer is a previleged one , then his ID will be found in a table say previlege_cust.

If this case is there then I want to deduct 1000 from his balance.Else 2000.

Moreover there are some more conditons like if his balance is less than 5000 , then deduce 100 more.

There are some more conditions.

Considering just two conditon as stated above.

Here's the output I want.

Customer_id            New_bal

1                             8000

2                              900

What I am doing here is storing the value of balance in peek(), and then using this value and customer_id to find the further deductions.

So, I want to output the value of a QlikView varaible.

Hope I am more clearer this time.

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

I got it now. My first approach would be to use a mapping table plus some conditionals in the script, so all the time it takes in load time you save it later in run time. Let's see if this example helps

DeductionsMap:
MAPPING LOAD Customer_ID,
     Deduction;
SQL SELECT Customer_ID, Deduction FROM Deductions;
ActualBalance:
LOAD Customer_ID,
     Balance AS Original_Balance,
     Balance - ApplyMap('DeductionsMap', Customer_ID, 2000) AS New_Balance;
SQL SELECT Customer_ID, Balance FROM Customer_Info;

This is a very simple example on how a mapping table works. You always need first to load the mapping table that allows only two fields. Then, in the second load, the ApplyMap() function means "give me the first parameter, that is Customer_ID in this case, and I will return the second, that is Deduction in this case, and if no customer is found, then use 2000 as default deduction value". You are now creating a new field, based on the balance from the customer info, minus the corresponding deduction according to the deductions table.

You can use conditionals and more mapping tables to classify your customers so you always have the right deduction for each user depending on their category.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#5]

Thanks Miguel !!!

But I am still wondering if we can display varibles from QlikView 

Because I have certain cases where I can't do the mapping as I need to use intermediate value of the fields to do query in other table.

Please throw some light on this issue .

[Miguel_Angel_Baeyens]

Hi,

Yes, you can use the content of a variable as a value in a new field:

LET vYear = Year(Today());
Table:
LOAD ID,
     Name,
     $(vYear) AS CurrentYear
FROM SourceFile.qvd (qvd);

It's a very dummy example, but it may help you on how to use a variable in a LOAD statement.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica