Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

intervigilium
New Contributor III

Combine fields AND Transpone?

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_TypeDescriptionSales JanuariSales FebruariSales MarchSales AprilSales May
AProduct A64069
A2Product A255583
BProduct B05375
B2Product B233447
CProduct C24233
C2Product C25360

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_TypeDescriptionSalesMonth
AProduct A6Januari
A2Product A25Januari
BProduct B0Januari
B2Product B23Januari
CProduct C2Januari
C2Product C25Januari
AProduct A4Februari
A2Product A25Februari
BProduct B5Februari
B2Product B23Februari
CProduct C4Februari
C2Product C23Februari
AProduct A0March
A2Product A25March
BProduct B3March
B2Product B24March
CProduct C2March
C2Product C26March
AProduct A6April
A2Product A28April
BProduct B7April
B2Product B24April
CProduct C3April
C2Product C20April
AProduct A9May
A2Product A23May
BProduct B5May
B2Product B27May
CProduct C3May
C2Product C22May

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

Thanks in advance!

1 Solution

Accepted Solutions
squeakie_pig
Contributor

Re: Combine fields AND Transpone?

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

2 Replies
squeakie_pig
Contributor

Re: Combine fields AND Transpone?

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

intervigilium
New Contributor III

Re: Combine fields AND Transpone?

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