topic Re: Num vers date in Francophones

Num vers date

Thu, 04 Jun 2015 10:59:42 GMT

Bonjour tout le monde,

j'ai un besoin un peu bizarre , en fait je veux récupérer un champs (de type variable càd peut être num , date , varchar....) à partir d'un fichier excel , le problème c'est que pour les valeurs de type date qlikview les interprète comme des numéro , sachant que j'ai beaucoup de date (date d'ouverture , date de fermeture , date de mise en marche ....)

je pense pas que la conversion dans le script en mettant la fonction date , va résoudre le pb puisque j'ai plusieurs date à traiter dans mon champs

y a t il un moyen pour que qlikview reconnait qu'il s'agit des types dates?

Merci

Re: Num vers date

maxgro — Thu, 04 Jun 2015 11:07:40 GMT

please post a little extract (some date,s num, varchar) of your excel and your script

or read here

Get the Dates Right

for date interpretation and formatting

Re: Num vers date

Thu, 04 Jun 2015 12:22:58 GMT

Thank you Massimo here is my excel ,

For my script i just load the file into my qlik's application , i need that qlikview reconize the date format :

Directory;

Valeur_Champs:

LOAD Id_v,

Id_ch as id_champs,

Nom,

valeur,

Id_poi

FROM

[..\..\valeur_cham.xlsx]

(ooxml, embedded labels, table is Feuil1);

Re: Num vers date

maxgro — Thu, 04 Jun 2015 13:10:43 GMT

maybe this

I added a new field using the value of field Nom to choose between date and number

Valeur_Champs:

LOAD Id_v,

Id_ch as id_champs,

Nom,

valeur,

Id_poi,

if(wildmatch(Nom, 'date*'), date(valeur), valeur) as NewValeur

FROM

........

Re: Num vers date

Thu, 04 Jun 2015 13:24:17 GMT

OR may be (if field value don't have Date in the name), try to test the length of the field (10 characters) + if there are 2 "/" or 2 "-"

Chris

Re: Num vers date

Thu, 04 Jun 2015 13:34:36 GMT

Yes your solution works , but may be i didn't explain my pb very well.

you based your script on the value of the field 'NOM' but , we can have more than 'NOM' which has o date type .

expl:

visité le : 12/11/2003

actualisation : 11/02/2015

dernière_facturation: 16/03/2009

....

i m searching for a solution based on the date type , because actually we have a dynamic values , that's why i have chosen excel because it support more than one type, but the pb is on the interpretation of date type via qlikview

and i would to clarify that the field 'NOM' will be moved on on other table 'NOM valeur' then i will mappe this table

with Valeur

Re: Num vers date

Thu, 04 Jun 2015 13:40:09 GMT

May be you can start using GENERIC function to determine DATE fields

here is an example

Chris

Re: Num vers date

Thu, 04 Jun 2015 13:52:49 GMT

Chris you forgot the example

thx

Re: Num vers date

Thu, 04 Jun 2015 13:56:18 GMT

Actually you have to adapt the script from qvw attached to your own needs

Chris

Re: Num vers date

maxgro — Thu, 04 Jun 2015 14:15:47 GMT

It seems to me Qlik get excel number and date as number

So I don't think you can decide if the value 40000 you load in Qlik was a number or a date in excel; hope to be wrong but I think you have to use a different rule (name of the field ?)

Re: Num vers date

Thu, 04 Jun 2015 14:28:00 GMT

thank you so much , if you find something helpfull it will be a pleasur if you shared it

Best regards