Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How can I get one simple month field if I have 2 month fields?

Hi Guys, How could I get one simple month field if I have 2 month fields from diferent tables? one month field comes from a crossed table.
I Attached the real Example but the example below is kind of close.
Than kou I appreciate your time Guys!!
Table1:
Load
Date
month(Date) as Month
from xlxs.
Table2:
Crosstable(Month, Numbers)
Load
Jan2012,
Feb2012,
Mar2012,
Abr2012,
May2012,
Jun2012,
Jul2012,
Ago2012,
Sep2012,
Oct2012,
Nov2012,
Dic2012
From xlxs
1 Solution

Accepted Solutions
MVP
MVP

Re: How can I get one simple month field if I have 2 month fields?

Hi Javier,

Since months are dual values (numeric values with string representation) try using 1, 2, 3 and so on instead "Ene", "Feb", "Mar", and so. You might need to do two loads so the format is actually a month. I have been looking at your script and it should look like this instead:

Ptto:

CrossTable(Mes_Ptto, Datos)

LOAD [Número de Parte]             as Material,

     ENE                           as 1,

     FEB                           as 2,

     MAR                           as 3,

     ABR                           as 4,

     MAY                           as 5,

     JUN                           as 6,

     JUL                           as 7,

     AGO                           as 8,

     SEP                           as 9,

     OCT                           as 10,

     NOV                           as 11,

     DIC                           as 12

FROM

[Ppto Entradas.xlsx]

(ooxml, embedded labels, table is [QV SR]);

I cannot reload and test because there is no excel file attached.

Hope that helps.

Miguel

EDIT: Definitely you need to do a two step load, because the field name is literal, not numeric. See attached application for further reference.

7 Replies
MVP
MVP

How can I get one simple month field if I have 2 month fields?

I haven't really looked into your attached sample app, I assume you just want to link the two field by name (Month). To get the two fields linked appropriately, the values should have the same format, this is what iI think is missing in your case:

Month(Date) is returning a dual, a text representation like Jan, Feb, Mar (depending on your sys settings), and the month number as numerical representation.

Your Month from Crosstable load will probably just read in as text, but you could convert it to the same format as in your first table:

Crosstable (Month, Numbers)

LOAD month(Date#(Month,'MMMYYYY')) as Month, OtherField1, OtherField2, ...

from Excel.xls;


Hope this helps,

Stefan

Not applicable

How can I get one simple month field if I have 2 month fields?

Hi Stefan, I think you are right, it should be something about format, but I can´t make it working, would you take a look at the App?

I´m sorry if I´m taking your time.

Tnaks!!

MVP
MVP

Re: How can I get one simple month field if I have 2 month fields?

Hi Javier,

Since months are dual values (numeric values with string representation) try using 1, 2, 3 and so on instead "Ene", "Feb", "Mar", and so. You might need to do two loads so the format is actually a month. I have been looking at your script and it should look like this instead:

Ptto:

CrossTable(Mes_Ptto, Datos)

LOAD [Número de Parte]             as Material,

     ENE                           as 1,

     FEB                           as 2,

     MAR                           as 3,

     ABR                           as 4,

     MAY                           as 5,

     JUN                           as 6,

     JUL                           as 7,

     AGO                           as 8,

     SEP                           as 9,

     OCT                           as 10,

     NOV                           as 11,

     DIC                           as 12

FROM

[Ppto Entradas.xlsx]

(ooxml, embedded labels, table is [QV SR]);

I cannot reload and test because there is no excel file attached.

Hope that helps.

Miguel

EDIT: Definitely you need to do a two step load, because the field name is literal, not numeric. See attached application for further reference.

Not applicable

Re: How can I get one simple month field if I have 2 month fields?

Hi miguel, Thank you for your reply, I create a Qvd file with the cross table, then after that I loaded it to the final app, I got just January and February values in just January, I´m missing something but I can´t figure it out.

I attached the app with this value and the source of this table Ptto.(Presupuesto).

Thanks!!

MVP
MVP

Re: How can I get one simple month field if I have 2 month fields?

Hi Javier,

Please check the file attached to my previous post. There I do exactly what you are looking for. You cannot use the MakeDate() function with a Num(), MakeDate() takes one parameter (year), two (year and month) or three (year, month and day) and returns a complete date in the form DD/MM/YYYY or whatever your regional date format is.

Instead, do a LOAD specifiying each of the fields as in my example above, then do a second reload (either RESIDENT as in the example, or using a QVD).

Hope that helps.

Miguel

Not applicable

Re: How can I get one simple month field if I have 2 month fields?

Hi Miguel, I did what you did in your App, but still not match the field Month with new one created. what Im doing wrong?

Not applicable

Re: How can I get one simple month field if I have 2 month fields?

I finally got the sollution, we were really close, the solution was, using the snetence in script :

MONTH(MAKEDATE(0, NUM#(Mes_Numerico))) as Mes,

Thank you Miguel, Thank you Stefan, You Guys saved my day!!

Have a grat Day!!

Community Browser