Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Scripting with qvd:s

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
9 Replies
eruditio
Contributor III

Re: Scripting with qvd:s

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,

E

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

Not applicable

Re: Scripting with qvd:s

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

eruditio
Contributor III

Re: Scripting with qvd:s

Hi Hakan,

Could you post a sample of your application?

Regards,

Antoine

Not applicable

Re: Re: Scripting with qvd:s

Hello Antoine,

here comes my app.

kindly

Håkan

Re: Scripting with qvd:s

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

eruditio
Contributor III

Re: Scripting with qvd:s

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

Not applicable

Re: Scripting with qvd:s

Hello again,

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

The problem is solved.

kindly

Håkan

eruditio
Contributor III

Re: Scripting with qvd:s


This is good.

For curiosity, what was the root cause?

Not applicable

Re: Scripting with qvd:s

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>

Community Browser