- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Two tables, two id's and names, only use first name
I have the following load in tab 1 i have
LOAD
SIAA.C_ID as [Customer ID],
SIAA.C_NAME as [Customer name]
FROM
[$(vDetailsQVDDirectory)\SIAA.qvd]
(qvd);
Then in tab 2 i have
PreIT:
Concatenate
LOAD
[Customer ID],
[Customer name]
FROM
[$(vDetailsQVDDirectory)\CTA.qvd]
(qvd);
What i want is if there is a matching Customer ID in both tabs i want it to only use the name from tab 1 (SIAA.C_NAME)
- Tags:
- concatenate
- load
- qvd
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
is there a mapping or apply map function to do this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Would you be able to create a small sample (with may be 10-15 lines of data for the two tables and the expected output you are aiming for? The problem is that we are not understanding the thing that you need.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I Think it would be easier making a two steps load, something like this:
// 1- Load all data of both tables
Customers_temp:
LOAD SIAA.C_ID as [Customer ID],
SIAA.C_NAME as Customer_name1
FROM [$(vDetailsQVDDirectory)\SIAA.qvd] (qvd);
Join
LOAD [Customer ID], [Customer name] as Customer_name2
FROM [$(vDetailsQVDDirectory)\CTA.qvd] (qvd);
// 2- Filter out the Customer Name
NoConcatenate
Customers:
LOAD [Customer ID],
if( Customer_name1 = null(), Customer_name2, Customer_name1) as [Customer name]
RESIDENT Customers_temp;
drop table Customers_temp;
// In case it takes too long (because you are working with a lot of records) you may want to store the data in a temporal qvd file instead of using the resident table, that should do the trick.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Matthew,
Pardon me if someone has already suggested this -- but could you add a flag that indicates the Source and then use the following expression to get what you need?
IF(COUNT(DISTINCT [Customer Name]) > 1, ONLY({<Source={'1'}>}[Customer Name]),ONLY([Customer Name]))
Please see attached for more details.
Thanks,
Camile
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Or if you prefer to refrain from using an if statement in an expression you could try the following (pushing the calculation back to the script):
If you put this new field as part of your schema (as a dimensional table) will allow you to easily use that field as a dimension.
See attached for more details.
Thanks,
Camile
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
use mapping laod and followed by apply map function
>applymap('Tab1',Cost_Name,'Null') as New_Cust_Name
hope this will help you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok this is the closest ive been!
Customer ID's and names working as they should, i just have an issue with values,
if there in table 1 its multiple quantitys and multiple dates and if there in table 1 and 2 its multiple quantitys and multiple dates which is fine.
however its only bringing through one quantity from 1 date if the ID is only in table 2.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I worked on a sample for you to show how concatenate would work:
Table1:
ID | Name | Invoice Date | Bert Code |
1 | John | 9/11/2015 | ABC |
2 | Alan | 3/23/2016 | DEF |
3 | Brett | 12/17/2015 | GHI |
4 | Carl | 3/23/2016 | JKL |
5 | Dan | 9/26/2015 | MNO |
6 | Elvis | 11/12/2015 | PQR |
7 | Fred | 11/16/2015 | STU |
8 | Gregg | 1/25/2016 | SHD |
9 | Han | 1/3/2016 | WME |
10 | Joe | 12/28/2015 | DFS |
Table2:
ID | Name | Invoice Date | Bert Code |
3 | Brett | 11/26/2015 | ABC |
4 | Carl | 10/6/2015 | DEF |
5 | Danny | 4/20/2015 | SKD |
6 | Elvis | 8/9/2015 | JKL |
7 | Fred | 12/7/2015 | MNO |
8 | Gregg | 4/5/2016 | SDW |
9 | Hannah | 1/31/2016 | STU |
10 | Joe | 7/10/2015 | FEF |
11 | Kris | 6/10/2015 | WME |
12 | Lee | 9/2/2015 | ASE |
Using this as input I use the following script:
Table:
LOAD ID,
Name,
[Invoice Date],
[Bert Code]
FROM
DATAA.xlsx
(ooxml, embedded labels, table is Sheet1);
Concatenate(Table)
LOAD ID,
Name,
[Invoice Date],
[Bert Code]
FROM
DATAA.xlsx
(ooxml, embedded labels, table is Sheet2)
Where not Exists(ID);
to get this output:
The logic is very simple here. If a ID exists in both table then Table2 data will be completely be ignored, but if it only exists in one of the two tables then it will be picked from that table.
I am attaching the qvw and the Excel file, please make changes to show what the issue is in your real scenario.
Best,
Sunny
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i dont want it to ignore table 2 data i just want it to use the name from table 1 if the ID is in both tables
why is this so difficult to do in qlikview?? in SQL its a 2 second job
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
We are just not sure of the kind of output you are looking for. Let me try this one more time.