Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Create a Condition using two fields which are in separate tables

Hi

I want to create a condition using two tabels which are in separate tables.  Let me elaborate.  I have the following 3 tabels:

1. Sales Data:        Containing Customer, Item Code and Sales Qty

2. Customer Data:  Customer and Customer Group

3.Item Data:            Item Code and Item Category

See attached Source Data and Qlikview Model.

I want to create a script that If the Customer Group is "Game" and the Item Category is "Sanware" , then this should be "Renewable" otherwise its "Non-Renewable" and this new field should be called Division.  This

Because Item Category and Customer Group are in 2 seperate tables, how do I combine these 2 fields.

kind regards

Nayan

Tags (1)
3 Replies
MVP
MVP

Re: Create a Condition using two fields which are in separate tables

Hi

You will have to join the tables to apply the condition, like this:

Data:

LOAD Customer, [Item Code], [Sales Qty]

FROM SalesData;

Left Join (Data)

LOAD Customer, [Customer Group]

FROM CustomerData;

Left Join (Data)

LOAD [Item Code], [Item Category]

FROM ItemData;

Final:

LOAD *,

  If([Customer Group] = 'Game' And [Item Category] = 'Sanware', 'Renewable', 'Non-Renewable') As Division

Resident Data;

DROP Table Data;

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Create a Condition using two fields which are in separate tables

Thank you Jonathan.  Will try it out on my actual Qlikview Model that i work with and let you know.

kind regards

Nayan

Not applicable

Re: Create a Condition using two fields which are in separate tables

Hi Jonathan

Your scripting works. Thank you. I have another situation

In my original data I have independent branches with the same data structure.

Attached herewith is the amended model which are link to 2 data sources. The original I rename Joburg and the other is Durban. Please note that there is an extra field called ‘Data Source”.

When I reloaded the model, ITEM Category, Customer Group and “Renewable” under Division does not appear for Durban. Please can you tell me where I am going wrong in my scripting.

Kind regards

Nayan

Sorry for the delayed reply, I had to leave for home and it was the weekend .

Community Browser