topic Re: Combine fields AND Transpone? in QlikView

Combine fields AND Transpone?

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

I have some data in a source where every month is a seperate column instead of a field with the date.

THis makes it very difficult to make a selection for some months (and use a master calender).

In excell everything explained (tab 1 and 2).

Current situation:


Product_Type	Description	Sales Januari	Sales Februari	Sales March	Sales April	Sales May
A	Product A	6	4	0	6	9
A2	Product A2	5	5	5	8	3
B	Product B	0	5	3	7	5
B2	Product B2	3	3	4	4	7
C	Product C	2	4	2	3	3
C2	Product C2	5	3	6	0	2

Now it is impossible/ very difficult to make a selection for a specific range of months. That is why i prefer :

Desired situation:

Product_Type	Description	Sales	Month
A	Product A	6	Januari
A2	Product A2	5	Januari
B	Product B	0	Januari
B2	Product B2	3	Januari
C	Product C	2	Januari
C2	Product C2	5	Januari
A	Product A	4	Februari
A2	Product A2	5	Februari
B	Product B	5	Februari
B2	Product B2	3	Februari
C	Product C	4	Februari
C2	Product C2	3	Februari
A	Product A	0	March
A2	Product A2	5	March
B	Product B	3	March
B2	Product B2	4	March
C	Product C	2	March
C2	Product C2	6	March
A	Product A	6	April
A2	Product A2	8	April
B	Product B	7	April
B2	Product B2	4	April
C	Product C	3	April
C2	Product C2	0	April
A	Product A	9	May
A2	Product A2	3	May
B	Product B	5	May
B2	Product B2	7	May
C	Product C	3	May
C2	Product C2	2	May

How can i solve this in some scripting / temp table?

Thanks in advance!

Re: Combine fields AND Transpone?

squeakie_pig — Mon, 24 Oct 2016 14:36:55 GMT

Which fields do you want to combine?

Unfortunately, you can't do a preceeding load on a crosstable, so you'll probably be looking for something like this. This will give you the output you want.

NewTable_temp:

CrossTable(Month, Sales, 2)

LOAD Product_Type,

Description,

[Sales Januari],

[Sales Februari],

[Sales March],

[Sales April],

[Sales May]

FROM [Sample_qlikview_forum_based_excel_V1_Selection_period.xlsx]

(ooxml, embedded labels, table is Source);

NewTable:

NoConcatenate

LOAD

subfield(Month, 'Sales ') as Month,

Sales,

Product_Type,

Description

RESIDENT NewTable_temp;

drop table NewTable_temp

Re: Combine fields AND Transpone?

intervigilium — Mon, 24 Oct 2016 18:26:03 GMT

Thanks Kenny,

Thanks for the solution!

I did't try it yet, but understand what you have done and that is absoluty the solution

Now you introduced the "crosstable" as solution,, i also found a tutorial: https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

Thanks for your time and i will implement it tomorrow