topic Load 'from_field' feature in QlikView

Load 'from_field' feature

Tue, 17 Nov 2009 22:51:40 GMT

Hello !

I'm confused about the newest Load 'from_field' on version 9.

Would someone else publish a mini QVW where we can see it in action, mostly explaining the concept ou reason underneath this new feature ?

On the other hand, many, many times I have a situation where I need to re-use a previously loaded field on a table,


USO:

LOAD
 LEFT(@1,1) AS CAMPUS_TC
 ,LEFT(@1,5) AS NOME_TC
 ,MID(@1,7,10) AS DATA_TC
 ,MID(@1,18,8) AS HORA_TC
 ,MID(@1,18,2) AS HH_HORA_TC
 ,MID(@1,27,30) AS OPCAO_KEY
 ,MID(@1,27,30) AS OPCAO_TC
 ,MID(@1,27,30) & ' ' & LOOKUP('DESCRICAO','OPCAO_DESC',MID(@1,27,30),'DESCRICOES') AS DESCRICAO_TC
 ,RECNO() AS RECNO_TC
 ,IF(MID(@1,7,10) > MAKEDATE(2008,11,18) AND MID(@1,27,07)='TC06699',MID(@1,35,6),NULL()) AS CARTAO_DER_TC
 ,IF(MID(@1,7,10) > MAKEDATE(2008,11,18) AND MID(@1,27,07)='TC06699',MID(@1,42,1),NULL()) AS GRUPO_KEY
 ,IF(MID(@1,7,10) > MAKEDATE(2008,11,18) AND MID(@1,27,07)='TC06699',MID(@1,42,1) & '/' & MID(@1,35,6) ,NULL()) AS GRUPOCAR_DER_TC
RESIDENT TEST WHERE NOT EXISTS(OPCAO_IGNORAR,MID(@1,27,30));
[/CODE]
Take a look at the expression MID(@1,7,10) , It's repeated several times on the load statement.
I just wanna be able to re-use a previously loaded field on the SAME load statement. On the example I wanna re-use DATA_TC again later on the script, without having to re-aply the expression to have the field content again.
That's it !
Thanks for your help !
 </body>

Load 'from_field' feature

Miguel_Angel_Baeyens — Tue, 17 Nov 2009 23:00:50 GMT

Hello Adriano,

It seems that from_field may help you with that re-use of fields. "from_field" is used when the field and the table were in a previously loaded file. Take into account, and please correct me if I'm wrong, that once you have loaded and not dropped a table into memory, you can use their field names as often s you want, which means, i.e.: that if you now make a new tab, and you want to let's say concatenate DATA_TC with a new field, it would be as easy as

,NEW_FIELD & "$$" & DATA_TC AS MODIFIED_DATA_TC

I will upload in a few minutes one simple example of from_field clause.

Regards.

Load 'from_field' feature

hector — Tue, 17 Nov 2009 23:06:16 GMT

Hi, if you want to "reuse" a calc field, put a load previous to the main load, something like this

USO: Load *, IF(DATA_TC > MAKEDATE(2008,11,18) AND MID(@1,27,07)='TC06699',MID(@1,35,6),NULL()) AS CARTAO_DER_TC ; LOAD LEFT(@1,1) AS CAMPUS_TC , LEFT(@1,5) AS NOME_TC , MID(@1,7,10) AS DATA_TC , MID(@1,18,8) AS HORA_TC , MID(@1,18,2) AS HH_HORA_TC , MID(@1,27,30) AS OPCAO_KEY , MID(@1,27,30) AS OPCAO_TC , MID(@1,27,30) & ' ' & LOOKUP('DESCRICAO','OPCAO_DESC',MID(@1,27,30),'DESCRICOES') AS DESCRICAO_TC , RECNO() AS RECNO_TC , //IF(MID(@1,7,10) > MAKEDATE(2008,11,18) AND MID(@1,27,07)='TC06699',MID(@1,35,6),NULL()) AS CARTAO_DER_TC //this will be calculated in the previous load, IF(MID(@1,7,10) > MAKEDATE(2008,11,18) AND MID(@1,27,07)='TC06699',MID(@1,42,1),NULL()) AS GRUPO_KEY , IF(MID(@1,7,10) > MAKEDATE(2008,11,18) AND MID(@1,27,07)='TC06699',MID(@1,42,1) & '/' & MID(@1,35,6) ,NULL()) AS GRUPOCAR_DER_TC RESIDENT TEST WHERE NOT EXISTS(OPCAO_IGNORAR,MID(@1,27,30));

RGDS and good luck

Load 'from_field' feature

Tue, 17 Nov 2009 23:18:10 GMT

OK.

But, I want to re-use 'CARTAO_DER_TC' on the second part of the LOAD statement.

Guess I can not do that.

Check this simple example . I want to 're-use' the VALUE_DER_TEST field :


TEST:

LOAD
 *
 ,IF(VALUE_TEST>100,'GORDO','MAGRO') AS VALUE_DER_TEST
;

LOAD
 *
 ,IF(VALUE_DER_TEST='GORDO','GRANDE','PEQUENO') AS TAMANHO_DER_TEST
FROM C:\TEST\QV\TEST123.CSV (ANSI, TXT, DELIMITER IS ';', EMBEDDED LABELS, NO QUOTES);

It is not accepted...

Load 'from_field' feature

hector — Tue, 17 Nov 2009 23:24:23 GMT

you have the sintax backwards

in the first load, you can read only fields from the second load, so in your case if VALUE_DER_TEST not exist in the

TEST123.CSV file, this will not work.

rgds,

PD. vc e brasileiro?

Load 'from_field' feature

Miguel_Angel_Baeyens — Tue, 17 Nov 2009 23:38:28 GMT

Take a look at this example:

TestTable:
Directory;
LOAD units,
 tens,
 hundreds
FROM
table.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);

LetterTable:
Directory;
LOAD [upper],
 [lower] & [upper] AS Combination//using a previously loaded field
FROM
table2.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);

On the other hand, I would recommend you to use inline or resident tables for those purposes.

Hope this helps!

Load 'from_field' feature

Tue, 17 Nov 2009 23:39:44 GMT

OK !

Now I understood !

Thank you !

PD:

Sim , sou brasileiro. Nascido em São Paulo mas atualmente moro e trabalho no interior do estado, em Piracicaba

(http://maps.google.com.br/maps?f=q&source=s_q&hl=en&geocode=&q=piracicaba&sll=-14.179186,-50.449219&sspn=112.084709,158.027344&ie=UTF8&hq=&hnear=Piracicaba+-+SP&z=11)

Trabalho na UNIMEP (Universidade Metodista de Piracicaba).

E vc ? Fala Português ? É de onde ?

Muito grato pela ajuda, embora o tópico original não tenha ainda sido respondido !

Abraço !

Load 'from_field' feature

hector — Tue, 17 Nov 2009 23:46:20 GMT

Beleza!, eu sou do Chile, e eu falo um pouco portugues.

Abraço

Load 'from_field' feature

Wed, 18 Nov 2009 00:02:45 GMT

Ah sim, muito bom !

Um pouco que sei do Chile:

- Bastante desenvolvido

- Grande produtor de cobre

- No sul do país teve colonização alemâ

- Cidades têm estilo europeu

- Baixíssimo índice de criiminalidade

- Fez a privatização do seu sistema de Previdência

- Tem uma mulher como presidente !

- Tem crescido bastante, economia bem desenvolvida

- Clima frio, claro ! rs

É isso !

Grande abraço !