Calculation - Resident Table

[#1]

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:

Amount	Description	Category1	Status1	Status2	New1	New2
100		A	X	Q	100	-
125		B	X	R	125	125
40		C	Y	R	-	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;

[swuehl]

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

Have you checked the content of your excel files?

[#3]

In Sheet2, Category1 is unique.

[sasiparupudi1]

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;

[Kushal_Chawda]

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

[#6]

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:

[Marcellino_Groothof]

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

Greetings

[sasiparupudi1 · Accepted solution]

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;

[#9]

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]

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