Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

row and column data changed?

Please help me,

I need to change the date row data column must be grouped code values for months,

Example:I have this table

  

DateÍtemQuantity
01-ene12310
10-ene12315
15-ene1005
25-ene10020
30-ene20010
02-feb12330
10-feb20015
18-feb20010
24-feb2005
27-feb10010

but I need this

 

ÍtemEneroFebrero
1002510
1232530
2001030

Thank you very much

4 Replies
MVP
MVP

Re: row and column data changed?

Try this script:

Table:

LOAD * Inline [

Date, Item, Quantity

01-Jan, 123, 10

10-Jan, 123, 15

15-Jan, 100, 5

25-Jan, 100, 20

30-Jan, 200, 10

02-Feb, 123, 30

10-Feb, 200, 15

18-Feb, 200, 10

24-Feb, 200, 5

27-Feb, 100, 10

];

TempTable:

LOAD Item,

  Sum(Quantity) as Jan

Resident Table

Where Month(Date#(Date, 'DD-MMM')) = 'Jan'

Group By Item;

Join (TempTable)

LOAD Item,

  Sum(Quantity) as Feb

Resident Table

Where Month(Date#(Date, 'DD-MMM')) = 'Feb'

Group By Item;

DROP Table Table;

Output in a table box object:

Capture.PNG

Employee
Employee

Re: row and column data changed?

Hi Rodrigo,

if you are using Qlik Sense, please see attached .qvf example.

There is an easier way to do this and it will support all the months you have in your data. I used a single wildmatch expression and a pivot table chart object.

Here is the script: (note I am using this discussion thread as a data source using the web file connector in Qlik Sense)

LOAD [Date],

    [Ítem],

    [Quantity]

  

FROM [lib://Rodrigo]

(html, codepage is 1252, embedded labels, table is @1);

In the master items I used:

if(wildmatch([Date],'*-ene*'),'ene','feb')

The used a Pivot Table object:

Please copy .qvf file to your C:\Users\<user profile>\Documents\Qlik\Sense\Apps and refresh Qlik Sense Desktop with F5

Let us know how you do.

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

ogster1974
Honored Contributor II

Re: row and column data changed?

This method will create you the month column from your data without hardcoding the month names.

This assumes your month identifiers are 3 chars in length.

Note you could do this during the data load or as I have done here created a Master dimension I can use later in other charts.

Not applicable

Re: row and column data changed?

Thank you very much

But,I have a table in the database and I have this SQL statement, but this bad, that's the idea ,

I do not know how to do it

Please help me)

data:
LOAD *;
SQL
SELECT DATE,ITEM,SUM(QUANTITY) AS "ENE"
FROM PDDTAEPM.DATA
WHERE (DATE BETWEEN 115001 AND 115031) //DATE JULIANA
GROUP BY ITEM;
 
STORE data INTO datasum.qvd(qvd);

DROP table data;