May i know how to make use of Loop command and load 100 raw data set ?

[paulyeo11]

Hi All

I have 100 company raw data.

Each of the company i need to load the below script :-

ARSummary:

LOAD left(FileBaseName(), 4) AS Report5_pay,

  'company_1' as SOURCE,       // This will help to filter company_1 to company_100

  @1:9T as [cust_id],

  @10:38T as [company_pay],

  if(right(@81:99T,1)='-', '-' & left(@81:99T, len(@81:99T)-1),@81:99T) as curren

FROM $(vRAWPath)$(vFile1) (ansi, fix, no labels, header is 0, record is line);     // File1 refer to company_1 raw data till file100 refer to company_100

Instead of i need to repear write the script 100 time. May i know how to make use of Loop command and load 100 raw data set ?

This will make maintenance easy.

Hope some one can advise me how to do it ?

Paul

[paulyeo11]

My Qvw & Raw data

[marcus_sommer]

Take a look here by the for- respectively for each loop: Loops in the Script.

- Marcus

[paulyeo11]

Hi Marcus

Thank you for the link. I will need to study .

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

[hic · Accepted solution]

I would use something like the following:

For each vFileName in FileList('$(vRAWPath)\*.txt')

ARSummary:

LOAD left(FileBaseName(), 4) AS Report5_pay,

  FileBaseName() as SOURCE,

  @1:9T as [cust_id],

  @10:38T as [company_pay],

  if(right(@81:99T,1)='-', '-' & left(@81:99T, len(@81:99T)-1),@81:99T) as curren

FROM "$(vFileName)" (ansi, fix, no labels, header is 0, record is line);  

Next vFileName

HIC

[paulyeo11]

Hi HIC

Many thank for your help.

I did try , and i notice that below feild does not load into QV.

FileBaseName() as SOURCE,

  @1:9T as [cust_id],

  @10:38T as [company_pay],

May i know where go wrong.

Paul

[paulyeo11]

Hi HIC

work fine now :-

Henric Cronström give me a script after reload it does not create the table.

Many thank. you help me move on, Otherwise i will struck there ...

Paul