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

[#1]

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

[swuehl]

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

[#3]

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

[Miguel_Angel_Baeyens · Accepted solution]

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.

[#5]

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

[Miguel_Angel_Baeyens]

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

[#7]

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?

[#8]

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

[kamikhan]

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