Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
Duplication of Amounts might occur if you have multiple lines in Sheet2 per Category1 value.
Have you checked the content of your excel files?
In Sheet2, Category1 is unique.
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;
Make sure that Your category 1 and category 2 fields have matched data else it will create Cartesian join
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:
You must use left join (Table_A) instead of join (this is inner join) . You want only a match between the files
Greetings
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;
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?
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