topic Re: Split columns into one column with several ids in QlikView

Split columns into one column with several ids

Tobschmi — Fri, 17 Apr 2020 12:03:05 GMT

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?

Re: Split columns into one column with several ids

rubenmarin — Fri, 17 Apr 2020 12:23:47 GMT

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.

Re: Split columns into one column with several ids

Tobschmi — Fri, 17 Apr 2020 13:29:58 GMT

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?

Re: Split columns into one column with several ids

rubenmarin — Mon, 20 Apr 2020 11:38:38 GMT

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;