problems with pick(match) statement

[#1]

I have just found a response from John Witherspoon to a question about the use of case statement, which he solved with the use of pick(1+match(variable, '1','2','3'), 'a','b','c'), and as I need to load a tabledependant on wich Month(date) I am, so to load sucha as

load *

sql select * from $(variable);

I have tested it, and works fine for me, but again, why does it do the work and tells me that cannot recognize the pick statement.

My use is pick(match(month,'ene',feb'...),'enero','febrero'...) as PreviousMonth; To do so gives me the previous month, I don't need the +1 before match, I use an if statement to be sure that $(PreviousMonth) has right values, as to say, not '' and not 'december' wich I don´t let load.

why does it say that doesn´t recognize pick statement.

JuanJo

[#2]

Can you please detail your question ?
I don't really understand what's your problem ?

Regards

[#3]

Also why do you use such a complex function to determine prevous month ?

What don't you use Date(AddMonths(month,-1),'MMM') as PreviousMonth ?

Rgds

[#4]

Well, I have tried pick(match to create a variable to set the PreviousMonth from actual load of a table, with this variable, PreviousMonth, I can open and load the table which name is in PreviousMonth, and it works

Load*; SQL Select any from $(PreviousMonth);

Fine it works, and the value inside PreviousMonth comes from pick(match(ActualMonth,'ene','feb',...),'enero','febrero'..) as PreviousMonth;

It works and loads the table referred in PreviousMonth, and the variable can be read, but at the end it tells me this

"Sentencia desconocida

pick(match(MesHoras,'ene','feb','mar','abr','may','jun','jul','ago','sep','oct','nov','dic'),'enero','febrero','marzo','abril','mayo','junio','julio','agosto','septiembre','octubre','noviembre','diciembre') as MesAnterior"

Wierd.

JuanJo is going home now to have lunch. See you later

[#5]

very good spastor. One collegue of mine told me that I was really a master making things difficult. 🙂

I cannot now, but I'll try it. thanks

JuanJo

[#6]

Well I need the exact name in caracters to open a table, 1,2,3 or jan,feb.mar doesn´t work, that's why I jumped on this example from John, the pick(match..

[#7]

spastor wrote:
Also why do you use such a complex function to determine prevous month ?
What don't you use Date(AddMonths(month,-1),'MMM') as PreviousMonth ?
Rgds<div></div>

Well, I have tried your suggestion, and adding more 'MMMMMMMMMMMM' to the date format, I get what I need, the full month's name.

In any case I cannot use this, because my idea doesn´t work. How can I determine if I have loaded values for that month, for that year?. I have tested the not exists (fecha) sentence, but of course it is not right, Could I make a selection as to that precise PreviousMonth, of that year, present year?

The thing is that all goes to an historic table from where I load all data, but there's a little space of time, two weeks maybe, where last month is out of historic into tempTable, till it´s checked, and make OK, and also, actual month is not in historic, this last or actual month I can load without any problem, and in order to have all data load, this is obviously a must, leaving it out makes data a little out of fase.

Maybe thinking the other way round helps. Make TempMonth table to be checked if it´s full or empty, which could solve the problem as to load or not, not to duplicate data. I'll work about it.

JuanJo

[#8]

I've just received an e-mail from qlik community, that shows my questions and contributions (none) .

And as I see this tread which I didn't solved, I want to thank everyone that make an effort to solve my problem.

After all I made my mind and changed my point of view, so I found another way to do it, not involving qlik but the way i presented data to qlik script, more easy way.

If someone thinks that I should use a single table for every record about "horas" and not one table for each month, there is a reason. If I use such a way is because, there's always one, two, several records which date is not of that month, usually from previous month, and then it wouldn't show as results of actual month in use.

I don't know if I have made me understood. Now I use, actual month, history and TempMonth to save last month for fifteen days till I put it into history and empty it.

Well, Tanks to every one, this is a big community.

JuanJo

El mensaje fue editado por: aJuanJoq ortografic corrections