Creating Synthetic keys

[#1]

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.

[jvitantonio]

In the second load:

Load * FROM
$(vDataPath)*.txt

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

[SunilChauhan]

in start of script

use

qualify *;

unqualify LensKey;

[CELAMBARASAN]

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

[hic]

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;

[stephencredmond]

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

[#7]

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.

[stephencredmond]

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

[#9]

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.

[stephencredmond]

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