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

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

Tue, 24 Jan 2012 16:19:24 GMT

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

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

swuehl — Tue, 24 Jan 2012 17:02:30 GMT

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

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

Tue, 24 Jan 2012 17:49:48 GMT

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!!

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

Miguel_Angel_Baeyens — Tue, 24 Jan 2012 17:58:18 GMT

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.

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

Tue, 24 Jan 2012 18:29:15 GMT

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!!

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

Miguel_Angel_Baeyens — Tue, 24 Jan 2012 20:46:04 GMT

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

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

Tue, 24 Jan 2012 22:34:44 GMT

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?

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

Wed, 25 Jan 2012 00:27:57 GMT

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!!

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

kamikhan — Thu, 24 Oct 2019 12:19:55 GMT

hi ... i have a one problem. i am fetching the data from mysql and then i have load in qlikview using cross table .. when i am using excel file for upload its simple for cross table . but now i am using mysql.
is any solution possible