topic Calculation - Resident Table in QlikView

Calculation - Resident Table

Fri, 21 Aug 2015 10:44:25 GMT

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	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;

Re: Calculation - Resident Table

swuehl — Fri, 21 Aug 2015 10:59:17 GMT

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

Have you checked the content of your excel files?

Re: Calculation - Resident Table

Fri, 21 Aug 2015 11:01:29 GMT

In Sheet2, Category1 is unique.

Re: Calculation - Resident Table

sasiparupudi1 — Fri, 21 Aug 2015 11:06:54 GMT

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

Kushal_Chawda — Fri, 21 Aug 2015 11:11:00 GMT

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

Re: Calculation - Resident Table

Fri, 21 Aug 2015 11:15:02 GMT

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:

Re: Calculation - Resident Table

Marcellino_Groothof — Fri, 21 Aug 2015 11:16:22 GMT

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

Greetings

Re: Calculation - Resident Table

sasiparupudi1 — Fri, 21 Aug 2015 11:16:56 GMT

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;

Re: Calculation - Resident Table

Fri, 21 Aug 2015 11:22:33 GMT

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?

Re: Calculation - Resident Table

sasiparupudi1 — Fri, 21 Aug 2015 11:28:36 GMT

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

Re: Calculation - Resident Table

Fri, 21 Aug 2015 11:31:35 GMT

Yes, thank you again for the solution and the explanation. Knowing this would be very helpful.

Cheers,

William