topic Re: Re: Uso de archivo .CSV in QlikView

Uso de archivo .CSV

Mon, 03 Feb 2014 13:37:29 GMT

Necesito utilizar un archivo plano ".csv", pero tengo dos problemas que necesito resolver.

1 ) Tengo dos columnas pero los datos vienen con el siguiente formato:

USER, NUMERO DE BLOQUEO

Juan, Fernandez, 2433

Pablo, Diaz, 890

El problema se me presenta cuando quiero subir el archivo, porque el delimitador del Campo USER es la coma, que es el mismo delimitador que tengo para la columna NUMERO.

2) Dentro del mismo archivo tengo diferentes datos para hacer diferentes indicadores, ambos separados por una fila vacia, por ejemplo (ademas de este ejemplo, se adjunta un ejemplo):

USER, NUMERO DE BLOQUEO

Juan, Fernandez, 2433

Pablo, Diaz, 890

USER, NUMERO DE DESBLOQUEO

Juan, Perez, 111

Diego, Diaz, 667

El problema es que no se como hacer para distinguir los datos para cada indicador.

Muchas gracias!!

Re: Uso de archivo .CSV

Aurelien_Martinez — Mon, 03 Feb 2014 13:55:46 GMT

Hi,

For your first problem you can do some think like that :

Load

@1 & ' ' & @2 as USER,

@3 as NUMERO

FROM myfile.csv (txt, codepage is 1252, no labels, delimiter is ',', msq)

;

And for the second, for the moment i have not a solution.

Best,

Aurélien

Re: Uso de archivo .CSV

fkeuroglian — Mon, 03 Feb 2014 14:01:23 GMT

Hola, te envio un adjunto para que veas la forma que debes cargar los csv en qlikview

cualquier cosa a las ordenes!

Fernando

Re: Uso de archivo .CSV

Mon, 03 Feb 2014 14:55:29 GMT

Aurélien,

excus me but I don't understand how I must use @1, @2 and @3...I must declare in some place before the Load?

Re: Re: Uso de archivo .CSV

Aurelien_Martinez — Mon, 03 Feb 2014 15:08:54 GMT

See this files.

Best,

Aurélien

Re: Re: Uso de archivo .CSV

Mon, 03 Feb 2014 15:27:55 GMT

THANKS FOR HELP WAS VERY USEFUL YOUR HELP!!!!!

Re: Uso de archivo .CSV

Aurelien_Martinez — Mon, 03 Feb 2014 15:29:47 GMT

And for the second part

Best,

Aurélien

Re: Re: Uso de archivo .CSV

Mon, 03 Feb 2014 18:00:30 GMT

Aurélien,

I was very useful help, but would need to make one last question, as I do to add a third column? Blocked, Blocked2, Blocked3?

Because I can not do, sorry but I'm new at this!

Thank you very much again.

Daniela

Re: Uso de archivo .CSV

Mon, 03 Feb 2014 20:49:57 GMT

Aurélien, Excuse me...this is my code, can you tell me where is the error, please?...

I need add third column more...

Thank you so much!!

DATA:

LOAD @1 & ' ' & @2 as USER,

@3 as NUMBER,

RowNo() as tt

FROM

[..\..\DATA\USR_FILE\Disponibilidad de servicios\4_Most_Active_Users_02-2013.csv]

(txt, codepage is 1252, no labels, delimiter is ',', msq, header is 1 lines);

NoConcatenate

TEST:

LOAD

Resident DATA

Where len(NUMBER)=0

;

LET vOther = Peek('tt', 0, 'TEST');

NoConcatenate

TOTO:

LOAD

USER,

NUMBER

Resident DATA

Where tt<$(vOther)

;

Inner Join(TOTO)

LOAD

USER,

NUMBER as NUMBER2

Resident DATA

Where tt>$(vOther)

;

Inner Join(TOTO)

LOAD

USER,

NUMBER as NUMBER3

Resident DATA

Where tt>$(vOther)

;

DROP Table DATA, TEST;

Re: Uso de archivo .CSV

Aurelien_Martinez — Tue, 04 Feb 2014 09:06:50 GMT

Hi,

I think it's work :

//You load your data

DATA:

LOAD @1 & @2 as USER,

@3 as NUMBER,

RowNo() as tt //this add a id of each row

FROM

agences.csv

(txt, codepage is 1252, no labels, delimiter is ',', msq, header is 1 lines);

//You keep the rows where you have no number

NoConcatenate

TEST:

LOAD

Resident DATA

Where len(NUMBER)=0

;

LET vOther = Peek('tt', 0, 'TEST'); //Save the number of the first row where you have not a data

LET vOther2 = Peek('tt', 1, 'TEST'); //Save the number of the second row where you have not a data

//For the first block (NUMBER)

NoConcatenate

TOTO:

LOAD

USER,

NUMBER

Resident DATA

Where tt<$(vOther)

;

//Second block (NUMBER2)

Inner Join(TOTO)

LOAD

USER,

NUMBER as NUMBER2

Resident DATA

Where tt>$(vOther) and tt<$(vOther2) //and this condition

;

//Add the third block (NUMBER4)

Inner Join(TOTO)

LOAD

USER,

NUMBER as NUMBER3

Resident DATA

Where tt>$(vOther2)

;

DROP Table DATA, TEST;

//If you have fourth Number you add a ohter variable like vOther (or vOther2) with 2 for the second parameter, add a

//condition in the NUMBER3 load statement and add a load for your NUMBER4.

Best,

Aurélien

Re: Uso de archivo .CSV

Tue, 04 Feb 2014 12:55:27 GMT

you're the best Aurélien!!

Thank you very much for your help, it was really very useful.

Re: Uso de archivo .CSV

Tue, 04 Feb 2014 14:34:04 GMT

Aurélien, Excus me but I was testing the code and does not bring me any data......Do you know Why ?

Thank you!

Re: Uso de archivo .CSV

Aurelien_Martinez — Tue, 04 Feb 2014 14:45:03 GMT

Daniela can you join a example of CSV?

Re: Re: Uso de archivo .CSV

Tue, 04 Feb 2014 15:02:27 GMT

Yes, sure.

This is the NEW .CSV with more data

THANK YOU!!!

Re: Re: Re: Uso de archivo .CSV

Aurelien_Martinez — Tue, 04 Feb 2014 15:17:04 GMT

Thanks, it is strange this script work find.

I have attached my QVW (and a I have changed Inner join by a join).

Re: Re: Re: Re: Uso de archivo .CSV

Tue, 04 Feb 2014 17:53:02 GMT

Aurélien,

you're right, that file works fine.
I have a file with more data and does not work, will not accept that much data?
I attach a picture and the file with complete data....

Thanks!!!

Re: Uso de archivo .CSV

Aurelien_Martinez — Wed, 05 Feb 2014 09:02:51 GMT

Hi Daniela,

Add " and wildmatch(USER, 'Username*') " like this :

NoConcatenate

TEST:

LOAD

Resident DATA

Where len(NUMBER)=0 and wildmatch(USER, 'Username*') //where you have no number and USER begin by Username

;

But you have some mistake on your data (it is for this reason you have trouble) like :

Recepción Gefco - Puerto Madero,61781

Guardia,7429

Best ,

Aurélien

Re: Uso de archivo .CSV

Wed, 05 Feb 2014 17:22:37 GMT

Thank you, so much for your help!