Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
how can we get table name using field value.
Clear enough now.
Join both table into one, and create additional fields of ServiceName. Later, use them as flags to find where they came from. Maybe like this:
Table:
LOAD DISTINCT
ServiceName,
ServiceName as SN1,
InstallationDate,
ExpiryDate
FROM System1;
OUTER JOIN (Table) LOAD DISTINCT
ServiceName,
ServiceName as SN2,
StartDate,
EndDate
FROM System2;
Result:
LOAD DISTINCT
ServiceName,
InstallationDate,
ExpiryDate,
StartDate,
EndDate
if(SN1=SN2, 'both', if(ServiceName=SN1, 'System1', 'System2')) as status
RESIDENT Table;
DROP TABLE Table;
Regards,
Michael
Could you please clarify what you mean, preferably with an example?
If assume you're asking about the Window Title (caption) of the table - you can use something like
=Field
But it will work only if one value of the field is selected.
He refers to the Name of a table in the script isnt it?
System1
ServiceName | Installation Date | Expiry Date |
Abc | 1/1/2011 | 1/1/2012 |
xyz | 2/2/2011 | 2/2/2013 |
pQR | 1/4/2011 | 12/5/2011 |
MNC | 2/2/2012 | 1/4/2013 |
bcD | 3/3/2014 | 1/1/2015 |
System 2
ServiceName | StartDate | EndDate |
aBc | 2/2/2011 | 5/5/2011 |
Mno | 3/3/2000 | 12/5/2000 |
SPr | 1/2/2009 | 1/2/2010 |
PqR | 5/5/2011 | 6/6/2011 |
Bcd | 2/4/2014 | 12/12/2015 |
mNC | 3/3/2012 | 5/5/2012 |
I want to merge these two tables based on service Name field without using upper function.
Then we have to add new field named Status it should filed with table name based on service name
Ex:
Service Name | installationsate | expiry date | start date | stopdate | status |
bcD | 3/3/2014 | 1/1/2015 | 2/4/2014 | 12/12/2015 | both |
xyz | 2/2/2011 | 2/2/2013 | null | null | System1 |
Load the tables in this way
System1:
Load
ServiceName,
InstallationDate,
ExpiryDate,
'System1' as Status
and
System2:
ServiceName,
StartDate,
EndDate,
'System2' as Status
and after that in the Status make it a derived field with expression.
=if(count(Status)>1, 'both', Status)
Clear enough now.
Join both table into one, and create additional fields of ServiceName. Later, use them as flags to find where they came from. Maybe like this:
Table:
LOAD DISTINCT
ServiceName,
ServiceName as SN1,
InstallationDate,
ExpiryDate
FROM System1;
OUTER JOIN (Table) LOAD DISTINCT
ServiceName,
ServiceName as SN2,
StartDate,
EndDate
FROM System2;
Result:
LOAD DISTINCT
ServiceName,
InstallationDate,
ExpiryDate,
StartDate,
EndDate
if(SN1=SN2, 'both', if(ServiceName=SN1, 'System1', 'System2')) as status
RESIDENT Table;
DROP TABLE Table;
Regards,
Michael
bcD & Bcd is different.... without using UPPER or LOWER function, how can we concatenate... Field name should be matching...
Correct, if not use upper or lower, they will be regarded as two separate values. Which they are.
but the result table shown in the question tell different story !!
How can we get...
Service Name | installationsate | expiry date | start date | stopdate | status |
bcD | 3/3/2014 | 1/1/2015 | 2/4/2014 | 12/12/2015 | both |
There are two questions here.
1. Can we Show service name without using upper/lower? The answer is yes. We can have an additional field just for presentation, without upper, and use upper for all other copies of the field.
2. Decide what to show, bcD or BCd. I don't know. I only can tell that it can't be both.
😞