topic Re: About Concatenation in QlikView

About Concatenation

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

Can you suggest some solutions to combine 2 tables to desired one.

Thanks for your attentions.

Re: About Concatenation

Peter_Cammaert — Tue, 18 Apr 2017 11:32:55 GMT

Can you post your source data as text instead of images? Saves time for a lot of members that are trying to come up with a solution.

Re: About Concatenation

jonas_rezende — Tue, 18 Apr 2017 13:08:28 GMT

Hi, THACH VO NGOC.

Excuse me. What's the logic in removing AA and BB, and keeping Full process FL?

Regards,

Jonas Melo.

Re: About Concatenation

Anonymous — Tue, 18 Apr 2017 14:00:19 GMT

Thanks for your reply Peter. Here is the text.

//NoConcatenate

//TableInline01:

//load * Inline

//[PQ,CBT.CC,PY.IDPY,CBT.Per

//NN GB-EPW ,GB-EPW,2277122771,1

//NN GB-EPW ,GB-PW,2277122771,2

//NN GB-EPW ,GB-GCB,2277122771,2

//NN GB-EPW ,GB-Wa DR,2277122771,2.5

//NN GB-EPW ,GB-Wa Ara NC,2277122771,2.5

//];

////

//load * inline

//[CBB.IDPY,CBB.CC,CBB.Per

//2277122771,GB-EPW,5

//2277122771,GB-PW,5

//2277122771,GB-GCB,5

//2277122771,GB-Wa Ara NC,5

//2277122771,Full process FL,89

//2277222772,AA,90

//2277222772,BB,10

//];

Re: About Concatenation

Anonymous — Tue, 18 Apr 2017 14:02:18 GMT

Hi Jonas,

We can use ,PY.IDPY or CB.IDPY

Thank for your reply

Re: About Concatenation

Peter_Cammaert — Tue, 18 Apr 2017 16:30:12 GMT

The code needed to produce a picture like this is ugly and probably can't be reliably applied to a larger data set.

Can you describe how these tables should be combined?

As Jonas already stated, there is something weird with the removal of AA & BB. Which leads me to conclude that only CBB.IDPY values that already exist in PY.IDPY should be retained, correct?

Re: About Concatenation

Anonymous — Tue, 18 Apr 2017 16:34:26 GMT

Hi Peter,

Yes, you are right, only CBB.IDPY values that already exist in PY.IDPY should be retained.

Re: About Concatenation

Peter_Cammaert — Tue, 18 Apr 2017 19:17:57 GMT

OK. Try this:

TableInline01:

LOAD * INLINE [

PQ,CBT.CC,PY.IDPY,CBT.Per

NN GB-EPW ,GB-EPW,2277122771,1

NN GB-EPW ,GB-PW,2277122771,2

NN GB-EPW ,GB-GCB,2277122771,2

NN GB-EPW ,GB-Wa DR,2277122771,2.5

NN GB-EPW ,GB-Wa Ara NC,2277122771,2.5

];

MapIDPY2PQ:

MAPPING LOAD DISTINCT [PY.IDPY], PQ RESIDENT TableInline01;

CONCATENATE (TableInline01)

LOAD ApplyMap('MapIDPY2PQ', [CBB.IDPY]) AS PQ,

[CBB.IDPY] AS [PY.IDPY], [CBB.CC] AS [CBT.CC], CBB.Per INLINE [

CBB.IDPY,CBB.CC,CBB.Per

2277122771,GB-EPW,5

2277122771,GB-PW,5

2277122771,GB-GCB,5

2277122771,GB-Wa Ara NC,5

2277122771,Full process FL,89

2277222772,AA,90

2277222772,BB,10

] WHERE Exists([PY.IDPY], [CBB.IDPY]);

TableFinal:

NOCONCATENATE

LOAD PQ, [CBT.CC], [PY.IDPY],

Sum([CBB.Per]) AS [CBB.Per], // Sum always returns a value

Sum([CBT.Per]) AS [CBT.Per]

RESIDENT TableInline01

GROUP BY PQ, [CBT.CC], [PY.IDPY];

DROP Table TableInline01;

It works for those few rows in your screenshot, but it's still ugly code.

And I'm almost certain that you'll be back with information about why it doesn't work in your situation (e.g. full data set)

Re: About Concatenation

Anonymous — Wed, 19 Apr 2017 14:17:51 GMT

Dear Peter, that's great ! I could apply your guide for my specific case.

Thank again for your honest and prompt support.