Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Calculation - Resident Table

Hi there,

I am new to qlikview and am struggling to get this working. I wonder if anyone can help with this (Thanks in advance):

I have a table_A [amount, description, category1] and another table_B [category1, status1, status2] from two different sources, using a join load in script (category1 will be the key), I can merge them in table_A [amount, description, category1, status1, status2];

Then I wish to want new variables based on the values of status1 and status2:

eg:

New1 and New2

if(Status1='X', Amount) as New1;

if(Status2='Q', Amount) as New2;

The end result should be something like this:

AmountDescriptionCategory1Status1Status2New1New2
100AXQ100-
125BXR125125
40CYR-40

These is the script I used, but it produces a lot of duplicates, please advice!

Table_A:

LOAD Amount,

     Description,

     Category1

FROM

(ooxml, embedded labels, table is Sheet1);

Join (Table_A)

LOAD Category1,

     Status1,

     Status2

FROM

(ooxml, embedded labels, table is Sheet2);

Load

if(Status1='X', Amount) as New1,

if(Status2='Q', Amount) as New2

Resident Table_A;

1 Solution

Accepted Solutions
sasiparupudi1
Honored Contributor III

Re: Calculation - Resident Table

Try this

Table_A:

LOAD Amount,

     Description,

     Category1

FROM

(ooxml, embedded labels, table is Sheet1);

Join (Table_A)

LOAD Category1,

     Status1,

     Status2

FROM

(ooxml, embedded labels, table is Sheet2);

noconcatenate

final:

Load *,

if(Status1='X', Amount) as New1,

if(Status2='Q', Amount) as New2

Resident Table_A;

drop table Table_A;

View solution in original post

10 Replies
MVP
MVP

Re: Calculation - Resident Table

Duplication of Amounts might occur if you have multiple lines in Sheet2 per Category1 value.

Have you checked the content of your excel files?

Not applicable

Re: Calculation - Resident Table

In Sheet2, Category1 is unique.

sasiparupudi1
Honored Contributor III

Re: Calculation - Resident Table

may be the category1 filed values are not identical?

try

Table_A:

LOAD Amount,

     Description,

     Trim(Category1) as Category1

FROM

(ooxml, embedded labels, table is Sheet1);

Join (Table_A)

LOAD Trim(Category1) as Category1

     Status1,

     Status2

FROM

(ooxml, embedded labels, table is Sheet2);

Load

if(Status1='X', Amount) as New1,

if(Status2='Q', Amount) as New2

Resident Table_A;

Re: Calculation - Resident Table

Make sure that Your category 1 and category 2 fields have matched data else it will create Cartesian join

Not applicable

Re: Calculation - Resident Table

Thank you for your reply, but this (trim) is not working either.

I do not know what Cartesian join is, but here is the result from my script:

help001.PNG

Marcellino_Groothof
New Contributor III

Re: Calculation - Resident Table

You must use left join (Table_A) instead of join (this is inner join) . You want only a match between the files

Greetings

sasiparupudi1
Honored Contributor III

Re: Calculation - Resident Table

Try this

Table_A:

LOAD Amount,

     Description,

     Category1

FROM

(ooxml, embedded labels, table is Sheet1);

Join (Table_A)

LOAD Category1,

     Status1,

     Status2

FROM

(ooxml, embedded labels, table is Sheet2);

noconcatenate

final:

Load *,

if(Status1='X', Amount) as New1,

if(Status2='Q', Amount) as New2

Resident Table_A;

drop table Table_A;

View solution in original post

Not applicable

Re: Calculation - Resident Table

Oh wow, that solves the problem! Thank you so much!

Is there a short explanation to what was the issue and why is it working now?

sasiparupudi1
Honored Contributor III

Re: Calculation - Resident Table

Glad that it solved the problem.

By default, qlikview tries to concatenate tables that are having identical fields.

so by saying noconcatenate, this action will be prevented and the two tables exist separately.

finally , after completing all the data manipulation like adding expressions etc in the resultant table,,we can drop the base table.

hth

Sasi