topic Re: Creating Synthetic keys in QlikView

Creating Synthetic keys

Wed, 15 Feb 2012 11:26:18 GMT

Hello,

Can anyone correct me. I developed the below code

LGSummary:
Load 
          LensKey,
          if(substringCount(Concat(LensResult,';'),'Fail')>0,'Fail','Pass') as LensResult
  Group By LensKey
;
Load * FROM
$(vDataPath)*.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
 Where 
 Date(CaptureDateTime,'MM-DD-YYYY hh:mm:ssTT') > Date($(vMaxCaptureDateTime),'MM-DD-YYYY')
 and 
Date(CaptureDateTime,'MM/DD/YYYY') >= Date(AddMonths(Today(),-12),'MM/DD/YYYY') 
;
Left Join (LGSummary)
LOAD 
     LensKey, CaptureDateTime 
 FROM
$(vDataPath)*.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
 Where 
 Date(CaptureDateTime,'MM-DD-YYYY hh:mm:ssTT') > Date($(vMaxCaptureDateTime),'MM-DD-YYYY')
 and 
Date(CaptureDateTime,'MM/DD/YYYY') >= Date(AddMonths(Today(),-12),'MM/DD/YYYY') 
;

- When we load this application. we should able to see the only the fields LensKey, LensResult, CaptureDateTime.

- But i can able to see all the fields of the text files. As show in the below image.

Creating Synthetic keys

jvitantonio — Wed, 15 Feb 2012 11:30:00 GMT

In the second load:

Load * FROM $(vDataPath)*.txt

You are loading the whole file. Just LOAD LensKey, LensResult

Creating Synthetic keys

SunilChauhan — Wed, 15 Feb 2012 11:32:44 GMT

in start of script

use

qualify *;

unqualify LensKey;

Re: Creating Synthetic keys

CELAMBARASAN — Wed, 15 Feb 2012 11:33:33 GMT

Hi,

I think you code must be like this..

LGSummary: Load LensKey, if(substringCount(Concat(LensResult,';'),'Fail')>0,'Fail','Pass') as LensResult Group By LensKey ; Left Join (LGSummary) LOAD LensKey, CaptureDateTime FROM $(vDataPath)*.txt (txt, codepage is 1252, embedded labels, delimiter is '\t', msq) Where Date(CaptureDateTime,'MM-DD-YYYY hh:mm:ssTT') > Date($(vMaxCaptureDateTime),'MM-DD-YYYY') and Date(CaptureDateTime,'MM/DD/YYYY') >= Date(AddMonths(Today(),-12),'MM/DD/YYYY') ;

Celambarasan

Creating Synthetic keys

hic — Wed, 15 Feb 2012 11:39:46 GMT

Don’t use wild cards in combination with the Left Join. You want to first load and concatenate several files, then join them onto LGSummary. But this QlikView does not understand. QlikView makes the join already after the first file.

Instead you could do the following:

For each vFile in FileList('$(vDataPath)*.txt')
XYZ:
LOAD
LensKey, CaptureDateTime
FROM [$(vFile)] ;
next vFile;

Left Join
Load * from XYZ;

Drop Table XYZ;

Creating Synthetic keys

stephencredmond — Wed, 15 Feb 2012 11:46:58 GMT

Hi,

The preceding load doesn't work with the *.txt. Load all the data into a temp table first then use resident.

Think you will need to do the same for the Join.

Regards,

Stephen

Re: Creating Synthetic keys

Wed, 15 Feb 2012 12:01:31 GMT

Hello All,

I tried what you said to me but still my problem is not able to solve. Actually i'm trying this for incremental process. But the increment for the qvd(i.e. VQDSummarizedDefects.qvd in VQDSummarizedDefects tab) file is not happening. The increment for RTQMain.qvd file is happening in my file.Here i'm attaching my file can anyone correct me.

More over i don't want to change the names of my fields.i.e with help of Qualify. OK if if change it should be replaced with original fields while generating qvd file.

Re: Creating Synthetic keys

stephencredmond — Wed, 15 Feb 2012 12:05:18 GMT

Just in brief, based on your original code, this will work:

Temp_Table:

Load * FROM

[$(vDataPath)*.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

;

LGSummary:

Load

LensKey,

if(substringCount(Concat(LensResult,';'),'Fail')>0,'Fail','Pass') as LensResult

Resident Temp_Table

Group By LensKey

;

drop table Temp_Table;

Temp_Table:

LOAD

LensKey, CaptureDateTime

FROM

$(vDataPath)*.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Left Join (LGSummary)

Load * Resident Temp_Table;

Drop Table Temp_Table;

Stephen

Re: Creating Synthetic keys

Wed, 15 Feb 2012 12:21:56 GMT

Stephen,

Ya your are right. But with help of resident load it is taking to load 7-8 hours. But according the code which i develop in the attached file it is very quick. Now i'm trying to optimize the time of reload. But the data is not coming exactly with this code.

Re: Creating Synthetic keys

stephencredmond — Wed, 15 Feb 2012 12:23:55 GMT

Hi,

If you are on a version before v10, then, instead of doing a resident load, write the Temp file to QVD first then load in the QVD - it is much faster. Shouldn't make a different in v10+.

Regards,

Stephen