topic Re: Joining/concatenating multiple fields into a table in QlikView

Joining/concatenating multiple fields into a table

tjagdev5 — Mon, 23 Jul 2018 17:43:22 GMT

Hi All!

Apologies if this is covered elsewhere but my forum trawling has not come up with much thus far

Not sure if I've described what I'm after properly in the title (it's my first time posting!) so I'll try to explain what I'm trying to do...:

I have a table (TableName9) that has separate data fields that I want to concatenate into 3 fields: Data_Type, Data_Delivery_Year, Data_Value.

From TableName9:

The data type fields I want to combine into the field Data_Type are:

Outcome_Measure_Type

Spend_Type

Contribution_Type

Gateway_Type

The delivery_year fields I want to combine into the field Data_Delivery_Year are:

Spend_Delivery_Year

Outcome_Measure_Delivery_Year

Contribution_Delivery_Year

The value fields I want to combine into the field Data_Value are:

Spend_Value

Outcome_Measure_Value

Contribution_Value

Gateway_Date

And I want to combine them all into TableName11 so that all the data sit under the three fields (Data_Type, Data_Delivery_Year, Data_Value)

Only thing is...when I use the below code, the values are all there ....but the Data_Type and Data_Delivery_Year fields are only filled out for Contributions. The other data does not seem to be pulling through even though I thought it should by left joining on RowID.

Any help would be gratefully received and I'm happy to provide any further info as needed

Many thanks in advance!
Taz

[$(vL.TableName11)]:
CROSSTABLE(Data,Data_Value,1)
LOAD RowID,
Contribution_Value,
Outcome_Measure_Value,
Spend_Value,
Gateway_Date
RESIDENT [$(vL.TableName9)];

LEFT JOIN ($(vL.TableName11))
LOAD RowID,
Contribution_Type AS Data_Type
RESIDENT [$(vL.TableName9)];

LEFT JOIN ($(vL.TableName11))
LOAD RowID,
Outcome_Measure_Type AS Data_Type
RESIDENT [$(vL.TableName9)];

LEFT JOIN ($(vL.TableName11))
LOAD RowID,
Spend_Type AS Data_Type
RESIDENT [$(vL.TableName9)];

LEFT JOIN ($(vL.TableName11))
LOAD RowID,
Gateway_Type AS Data_Type
RESIDENT [$(vL.TableName9)];

LEFT JOIN ($(vL.TableName11))
LOAD RowID,
Contribution_Delivery_Year AS Data_Delivery_Year
RESIDENT [$(vL.TableName9)];

LEFT JOIN ($(vL.TableName11))
LOAD RowID,
Outcome_Measure_Delivery_Year AS Data_Delivery_Year
RESIDENT [$(vL.TableName9)];

LEFT JOIN ($(vL.TableName11))
LOAD RowID,
Spend_Delivery_Year AS Data_Delivery_Year
RESIDENT [$(vL.TableName9)];

DROP TABLE [$(vL.TableName9)]

Re: Joining/concatenating multiple fields into a table

chriscammers — Mon, 23 Jul 2018 18:27:26 GMT

I think you are missing an important detail. When you join qlik uses whatever common fields as the join condition so in your case probably only the first join for each type adds any values however I think you are not going to get the results you needed.

Are you looking to have fields where the values are a delimited list of the parts? This is what I think you are looking for.

[$(vL.TableName11)]:
CROSSTABLE(Data,Data_Value,1)
LOAD RowID,
Contribution_Value,
Outcome_Measure_Value,
Spend_Value,
Gateway_Date

RESIDENT [$(vL.TableName9)];

LEFT JOIN ($(vL.TableName11))
LOAD RowID,
Contribution_Type & '|' & Outcome_Measure_Type & '|' & Spend_Type & '|' & Gateway_Type AS Data_Type,

Contribution_Delivery_Year & '|' & Outcome_Measure_Delivery_Year & '|' & Spend_Delivery_Year AS Data_Delivery_Year

RESIDENT [$(vL.TableName9)];

DROP TABLE [$(vL.TableName9)]

Re: Joining/concatenating multiple fields into a table

tjagdev5 — Tue, 24 Jul 2018 06:52:39 GMT

Hi Chris,

This is fantastic - exactly what I was looking for! I'll just remove the | characters post-load and I'll be all good to go.

Many thanks for your help!!

Taz