csv extract

Tue, 04 Sep 2012 13:54:13 GMT

Hi!

I need to extract fields in a file(csv).

The csv fields:

# 20120717-20120717

2,30.862,45.832,63.207

,30.862,45.832,63.207

When "20120717" is Date, "20120717" is DateA,"2" is Day, "30.862" is View and "63.207" is ViewT

I try

VISISTANTES:

LOAD @1
FROM

(txt, utf8, no labels, delimiter is ';', msq);

Calculo:
NoConcatenate
LOAD
IF (IsNum (Right (@1,3)) and
IsNum (Right (@1,4)) and
IsNum (Right (@1,5)) and
IsNum (Right (@1,6)) and
IsNum (Right (@1,7)) and
IsNum (Right (@1,8)) and
IsNum (Right (@1,9)) ,mid(@1,3,8) ) as Date

IF (IsNum (Right (@1,3)) and
IsNum (Right (@1,4)) and
IsNum (Right (@1,5)) and
IsNum (Right (@1,6)) and
IsNum (Right (@1,7)) and
IsNum (Right (@1,8)) and
IsNum (Right (@1,9)) ,mid(@1,12,17) )as DateA,

IF( IsNum(Left(@1,1)), Left(@1,1)) as Day,

IF(IsNum(mid(@1,2,1)),mid (@1,2,6)) AS View,
IF(IsNum(mid(@1,2,1)),mid (@1,9,6)) AS ViewA,
IF(IsNum(mid(@1,2,1)),mid (@1,16,6)) AS ViewT
Resident VISISTANTES
;

DROP Table VISISTANTES;

This is good extract but this has a default, the qvd generate is complete in null.

Day	Date	DateA	View	ViewA	ViewT
2
	20120717	20120717
			63.207	45.832	30.862

I need all in a file.

Day	Date	DateA	View	ViewA	ViewT
2	20120717	20120717	63.207	45.832	30.862

What is the problem?

Regards!.

Ivan Troisi

Re: csv extract

Miguel_Angel_Baeyens — Tue, 04 Sep 2012 13:59:12 GMT

Hi,

What about using SubField() instead? Take a look at:

Visitantes:
LOAD SubField(SubField(@1, ',', 1), '-', 1) AS Date,
     SubField(SubField(@1, ',', 1), '-', 1) AS DateA,
     SubField(@1, ',', 2) AS Day,
     SubField(@1, ',', 3) AS View,
     SubField(@1, ',', 3) AS ViewT
FROM ...

Feel free to post a sample file with some lines to get that in a working QVW file.

Hope that helps.

Miguel

Re: csv extract

Tue, 04 Sep 2012 14:22:29 GMT

I try using SubField but the csv contains garabage data. Add cvs file. It's possible using subfield in this file?

topic Re: csv extract in QlikView

csv extract

Re: csv extract

Re: csv extract