Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
chesterluck
Contributor II

Preparation / Loading of Data - General approach

Hello QlikCommunity

I don't know what is the right way to load and cleanse the data.

What I did first is to load the data 1:1 and store it in qvd s.

Then I load the qvd s and join, cleanse .. the data.

Here is some code to show what i mean:

-------------------------------------------------------------------------------------CODE-----------------------------------------------------------------------------

Register 1 :

 

TOL_NEW:
LOAD lTolRef,
    
lTolTypRef,
    
sDepot,
    
sDescript,
    
sNameFROM
..\..\02_Data\TOL_MI.qvd
(
qvd)Where ( (lTolTypRef = 10) OR (lTolTypRef = 20) OR (lTolTypRef = 30) )
;

Left Join
LOAD
     
lTolRef,
     
lJobRef//      , 
//     lTolTypRef,
//      tBeginFROM
..\..\02_Data\TOL_BOK_MI.qvd
(
qvd);

 

Store

TOL_NEW into

..\..\02_Data\TOL_New_MI.qvd;

Redister 2:

JOB_NEW:

LOAD
dSetQuant,
    
dSetShots,
    
dSetSpeed,
    
iActCavity,
    
iSetCavity,
    
lJobRef,
    
sName as Auftragsnummer,
    
sType,
    
tActBegin,
    
tActEnd,
    
tReleaseDate
FROM
..\..\02_Data\JOB_MI.qvd
(
qvd);

Left Join

LOAd


      
lTolRef,
    
lTolTypRef,
    
sDepot,
    
sDescript,
    
sName as TOL_sName,
    
lJobRef
    
From ..\..\02_Data\TOL_New_MI.qvd
(
qvd);

Store JOB_NEW
into ..\..\02_Data\JOB_New_MI.qvd;

Register 3:

LOAD dSetQuant,
    
dSetShots,
    
dSetSpeed,
    
iActCavity,
    
iSetCavity,
    
lJobRef,
    
Auftragsnummer,
    
sType,
    
tActBegin,
    
tActEnd,
    
tReleaseDate,
    
lTolRef,
    
lTolTypRef,
    
sDepot,
    
sDescript,
    
TOL_sName
FROM
..\..\02_Data\JOB_NEW_MI.qvd
(
qvd);

-------------------------------------------------------------------------------------CODE-----------------------------------------------------------------------------

1. Do I have to save my loaded data into qvd s in order to work wiith them later on?

2. I dont really know how to drop the Tables

//Drop Table TOL_NEW; 

was not working on register nr 1

so I was always using the script :

let numTables = NoOfTables();for i=1 to $(numTables)let tt = TableNAme(0);drop table [$(tt)];next

3) Am  I on the right way or are there some bigger mistakes in my procedure??

Thanks in advance!

chesterluck

1 Reply
MVP & Luminary
MVP & Luminary

Re: Preparation / Loading of Data - General approach

1. No, it's not necessary, but it can be a good idea as a store and load to and from a qvd may be faster than using a resident table.

2. Drop Table TOL_NEW; is the correct syntax. So that should work as long as you keep case sensitivity in mind.

3. Looks ok to me as far as I can tell.


talk is cheap, supply exceeds demand