Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author

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
Author

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

Anonymous
Not applicable
Author

Hi Hakan,

Could you post a sample of your application?

Regards,

Antoine

Not applicable
Author

Hello Antoine,

here comes my app.

kindly

Håkan

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

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
Author

Hello again,

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

The problem is solved.

kindly

Håkan

Anonymous
Not applicable
Author


This is good.

For curiosity, what was the root cause?

Not applicable
Author

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>