I am joining two tables and attempting to create a calculated field that would be a combination of one field from each table, but after having researched solutions on the community, I am still not able to return my desired results.
Here is the gist of my current script -
[Providers]:
LOAD
[Provider],
[Dept]
FROM [lib://list.xlsx]
(ooxml, embedded labels, table is [Providers]);
[Charges Export]:
Right join LOAD
[Provider], [Entity], [Charges]
FROM [lib://Charges.xlsx]
(ooxml, embedded labels, header is 2 lines, table is [Charges Export]);
The two tables join on the provider field. Now that I have the tables joined, I want to combine the Entity field from the Charges Export with the Dept field from the Providers table, but am unsure as of how to do this in Qlik syntax.
Hi Jwjackso, thanks for the response. The [Entity] and [Dept] fields are separate sets of data with no overlap, so when I renamed [Entity] as [Dept] this essentially made a union of those fields. I would instead like to combine the two fields as [Entity]&[Dept] in a combined field while retaining both the [Entity] and [Dept] fields separately in the table.
Do you know how I do this? Do I have to create some sort of third resident load?
[Providers]: LOAD [Provider], [Dept] FROM [lib://list.xlsx] (ooxml, embedded labels, table is [Providers]);
[Charges Export]: Right join LOAD [Provider], [Entity], [Charges] FROM [lib://Charges.xlsx] (ooxml, embedded labels, header is 2 lines, table is [Charges Export]);
// Creating a new table that includes the concatenated field FinalTable: LOAD *, [Entity] & ' - ' & [Dept] AS Entity_Dept_Combined // Concatenating fields with a separator RESIDENT [Providers];
Right joining the two tables using [Provider] as the key you create a new FinalTable using RESIDENT to reference the joined table and add the new calculated field [Entity] & ' - ' & [Dept] AS Entity_Dept_Combined, which combines the two fields with a separator (' - ' for readability, but you can use any character).
This way, you'll retain both [Entity] and [Dept] while also having the new combined field [Entity_Dept_Combined].
Something like this. Create a temporary table joining the data together. Then process that data into another table building your combined field. Once you have your new field, drop the temporary table.
TempTable: LOAD [Provider], [Dept] FROM [lib://list.xlsx] (ooxml, embedded labels, table is [Providers]); Right join LOAD [Provider], [Entity], [Charges] FROM [lib://Charges.xlsx] (ooxml, embedded labels, header is 2 lines, table is [Charges Export]);
NoConcatenate
NewTable: Load *, [Dept]&[Entity] as [Combined Field] Resident Temptable;
Since you have not provided any data this is difficult to answer. First, verify that you query returns data for the three fields in question. Secondly, make sure your fields are test fields an not numeric. If they are numeric, concatenation will fail. Thirdly, I typically use & to concatenate vs +. To me it is a better representation.
If your fields are not text fields, you can convert them. text("hds_dtc") & ' ' & Text("hash_vin") & ' ' & Text("charge_session") as "derived_sug".
