topic Re: Scripting with qvd:s in QlikView

Scripting with qvd:s

Thu, 06 Feb 2014 11:33:51 GMT

Invoices:

load * from I:\Statistics\QVA\Datafiles\2012\StatKomp2012_gen.qvd (qvd);
load * from I:\Statisticsk\QVA\Datafiles\2013\StatKomp2013_gen.qvd (qvd);
join load cunumber,Salesman1 as CR_Sales1,Salesman2 as CR_Sales2  resident Customer_read;

This gives me both salesmen in every record but I will have only the one that correspod
to the inoice type  in inv_type 


In my earlier script where I was joining the salesman in an temptable 

Invoice_Temp:
Load [@1:8T] as inv_period1,
.
.
from I:\Statistics\QVA\Datafiles\2012\stat12_gen.txt (ansi, fix, no labels, header is 0, record is line)
 where [@216:223T]<>'GE';
 join load cunumber,Salesman1,Salesman2 resident Customer_read;
 
 
 and in the next read I had an if-sentence
 
 Invoices:
Load inv_period1 as inv_period,
inv_type1 as inv_type,
.
.
if(inv_type1='L',Salesman1,Salesman2) as inv_salesman
resident Fakturor_Temp
  where left(StatHierarki1,2)<>'17' ;
  
  
  This inv_salesman was then used in an Load * Inline
  
  Salesman:
  Load * Inline [
  inv_salesman, salesman
  SE999900, No allocation
  SE999901, RegionA_G
  SE999902, RegionB_G
  SE999903, RegionB_F
  SE999904, RegionC_G
  SE999905, No allocation
  SE999906, RegionC_F
  '        ', No allocation
];

When I load the qvd-files I have no connection to the field inv_type as I have when I load
from the txt-file. 
Is there any other way to put the right salesman to the record in the table Invoices?

kindly
Håkan

Re: Scripting with qvd:s

Anonymous — Sat, 08 Feb 2014 14:22:16 GMT

Hello Hakan,

Try this

Map_salesman1:

MAPPING LOAD

cunumber,

Salesman1

Resident Customer_read;

Map_salesman2:

MAPPING LOAD

cunumber,

Salesman2

Resident Customer_read;

Invoices:

LOAD

* ,

if(inv_type1='L', ApplyMap ('Map_salesman1', cunumber, 'No salesman 1'), ApplyMap ('Map_salesman2', cunumber, 'No salesman 2')) as Invoice_SalesMan,

FROM I:\Statistics\QVA\Datafiles\2012\StatKomp2012_gen.qvd (qvd);

LOAD

if(inv_type1='L', ApplyMap ('Map_salesman1', cunumber, 'No salesman 1'), ApplyMap ('Map_salesman2', cunumber, 'No salesman 2')) as Invoice_SalesMan,

FROM I:\Statisticsk\QVA\Datafiles\2013\StatKomp2013_gen.qvd (qvd);

Cheers,

/*Edit: didn't see the invoice type = 'L' condition. that should work

Re: Scripting with qvd:s

Mon, 10 Feb 2014 09:20:06 GMT

Hello,

thanks for the response but I dont get the salesman number into the field 'Invoice_Salesman' though the ''No salesman 1' not is the value of Salesman1 in the mapping. If I use it as it is now I only get the text 'Salesman1' or

'Salesman2' . How do I get the values from Salesman1 and Salesman2 into Invoice_SalesMan?

kindly

Håkan

Re: Scripting with qvd:s

Anonymous — Mon, 10 Feb 2014 10:24:17 GMT

Hi Hakan,

Could you post a sample of your application?

Regards,

Antoine

Re: Re: Scripting with qvd:s

Mon, 10 Feb 2014 13:51:36 GMT

Hello Antoine,

here comes my app.

kindly

Håkan

Re: Scripting with qvd:s

Peter_Cammaert — Mon, 10 Feb 2014 14:13:40 GMT

Note that if you ever want to publish this document on a QlikView server, you should replace the QVD paths with corresponding UNC paths. Drive I: almost certainly won't exist for the service account on a QlikView Server.

Even better, store the path(s) in variables at the front of your script and use the variables throughout your tabs. Makes maintenance a lot easier when locations change.

Best,

Peter

Re: Scripting with qvd:s

Anonymous — Mon, 10 Feb 2014 15:35:35 GMT

Hello Hakan,

The mapping seems to work well. I suspect a data issue in the source files.

Try going back to the TXT file and isolate the result.

When i do the following:

Map_salesman1:

LOAD

kundnr1,

SäljareGlas as Salesman1

FROM customer.qvd(qvd);

Map_salesman2:

LOAD

kundnr1,

SäljareBågar as Salesman2

FROM customer.qvd(qvd);

I get the same results as when I apply the map.

This is what I get in the invoice salesman field.

SE999900

SE999901

SE999902

SE999903

SE999904

SE999905

SE999906

Re: Scripting with qvd:s

Wed, 12 Feb 2014 13:40:27 GMT

Hello again,

now it works fine. Thanks for all support all of you.

The problem is solved.

kindly

Håkan

Re: Scripting with qvd:s

Anonymous — Wed, 12 Feb 2014 15:04:10 GMT

This is good.

For curiosity, what was the root cause?

Re: Scripting with qvd:s

Thu, 13 Feb 2014 07:18:09 GMT

Hello,

I had a mix of reading this years file as a text file and there add the salesmen by Apply Map and reading qvd-files with the Apply Map.

In this case it didn´t work. Now I created a ‘dirty solution’, first reading the text file from this year into an qvd-file and then load it

as the other qvd-files.

I have an general problem with these text files with a version higher than 11.00 so I have to create the qvd:s and all calculations in this

version and then do the layout in higher versions. I don´t exactly now what is causing this.

kindly

Håkan

Håkan Fogelin

RSE/IT

IT-manager

Tel: +46 (0)18188005

E-Mail: hakan.fogelin@rodenstock.se

Rodenstock Sverige AB

Fålhagsleden 61

753 23 Uppsala

www.rodenstock.se<http://www.rodenstock.se/>

Facebook<http://www.facebook.com/Rodenstock> | Twitter<http://twitter.com/Rodenstock> | YouTube<http://www.youtube.com/SeeBetterRodenstock> | Blog<http://www.rodenstock-blog.com/>

Från: eruditio

Skickat: den 12 februari 2014 16:04

Till: Fogelin, Håkan

Ämne: Re: - Scripting with qvd:s

Qlik Community<http://community.qlik.com/>

Scripting with qvd:s

reply from eruditio<http://community.qlik.com/people/eruditio?et=watches.email.thread> in Scripting - View the full discussion<http://community.qlik.com/message/467135?et=watches.email.thread#467135>