Split columns into one column with several ids

[Tobschmi]

Hello, everyone,

I have a supposedly simple problem. I have data that looks like this:

ID	component1	component2	component3
1	100	101	102

What I want is something like this:

ID	component
1	100
1	101
1	102

I think I can load it with Resident and Inline, unfortunately I can't make it, could someone help me?

[rubenmarin · Accepted solution]

Hi, you can do a crosstable, in example:

CrossTable (componentName, component)
LOAD * Inline [
ID,	component1,	component2,	component3
1,	100,	101,	102
];

DROP Field conponentName

Instead of inline use your data source.

[Tobschmi]

Hi,

that has already helped a lot! Unfortunately, I can't get any further on the second one, because I have two databases that I want to merge via Concatenate:

components:
CrossTable (components, component)
LOAD
  'LIN_'& Variation.id  as %VariationId,
  SubField(SubField(VariationBundle.components, ';', 1), ':', 1) as VariationBundle.components1,
  SubField(SubField(VariationBundle.components, ';', 2), ':', 1) as VariationBundle.components2,
  SubField(SubField(VariationBundle.components, ';', 3), ':', 1) as VariationBundle.components3

FROM [lib://QVD (ssc-qlik_qlik)/Items_LIN/Items_Lin.qvd]
(qvd);


Unqualify 'components.*';
Concatenate (components)
CrossTable (components, component)
LOAD
  'SSC_'& Variation.id  as %VariationId,
  SubField(SubField(VariationBundle.components, ';', 1), ':', 1) as components.VariationBundle.components1,
  SubField(SubField(VariationBundle.components, ';', 2), ':', 1) as components.VariationBundle.components2,
  SubField(SubField(VariationBundle.components, ';', 3), ':', 1) as components.VariationBundle.components3

FROM [lib://QVD (ssc-qlik_qlik)/Items_SSC/Items_Ssc.qvd]
(qvd);

But that's how I get it wrong: Illegal combination of prefixes

I guess I have to make a JOIN here, right? Unfortunately, I'm not getting anywhere on my own. Anyone else have a tip?

[rubenmarin]

Hi, that error is about using concatenate and crosstable in the same step, try doing it with different steps:

// Load second table data
tmpSecond:
Crosstable...

// Add tmp table to components and delete
Concatenate (components) LOAD * Resident tmpSecond;
DROP Table tmpSecond;