How to combine 3 similar set of script into 1 ?

[paulyeo11]

Hi All

I have 3 set of completely similar script , only different is the Excel Sheet Name . May i know how to convert 3 of them into 1 for easy maintenance ?

PRICE:

LOAD

     BRAND_P,

     Model as PART_NO_,

     Description,

     [PRODUCT_LINE] as [PRODUCT_TYPE],

     [PRODUCT_LINE],

     Supplier,

     [Product Group],

     [LOCAL PRODUCT],

     Currency_P,

     date([Updated]) as Updated,

     Price as LIST,

     Price as LIST_PRICE

FROM

[$(vRAWPath)$(vFILE1)]

(biff, embedded labels, table is SHEET1$);

LOAD

     BRAND_P,

     Model as PART_NO_,

     Description,

     [PRODUCT_LINE] as [PRODUCT_TYPE],

     [PRODUCT_LINE],

     Supplier,

     [Product Group],

     [LOCAL PRODUCT],

     Currency_P,

     date([Updated]) as Updated,

     Price as LIST,

     Price as LIST_PRICE

FROM

[$(vRAWPath)$(vFILE1)]

(biff, embedded labels, table is SHEET2$);

LOAD

     BRAND_P,

     Model as PART_NO_,

     Description,

     [PRODUCT_LINE] as [PRODUCT_TYPE],

     [PRODUCT_LINE],

     Supplier,

     [Product Group],

     [LOCAL PRODUCT],

     Currency_P,

     date([Updated]) as Updated,

     Price as LIST,

     Price as LIST_PRICE

FROM

[$(vRAWPath)$(vFILE1)]

(biff, embedded labels, table is SHEET3$);

[paulyeo11]

Enclosed my Raw Data file from excel.

[paulyeo11]

Finally my QV doc.

[Sokkorn]

Hi Paul,

You may try this:

FOR Each vSheet in 'SHEET1$','SHEET2$','SHEET3$'

  LOAD

      BRAND_P,

      Model as PART_NO_,

      Description,

      [PRODUCT_LINE] as [PRODUCT_TYPE],

      [PRODUCT_LINE],

      Supplier,

      [Product Group],

      [LOCAL PRODUCT],

      Currency_P,

      date([Updated]) as Updated,

    Price as LIST,

      Price as LIST_PRICE

  FROM

  [$(vRAWPath)$(vFILE1)]

  (biff, embedded labels, table is $(vSheet));

Next vSheet;

Regards,

Sokkorn

[paulyeo11]

Hi Sok

i try it not working. Must have miss comma some where.

[paulyeo11]

Ho Sok

vSheet how should i declare ?

Paul

[Sokkorn]

Hi Paul,

No need to declare vSheet. See my attached image

Regards,

Sokkorn

[paulyeo11 · Accepted solution]

Hi Sok

Thank you now working fine.

Not sure why after i add lET vSheet=1; it work fine. below is the script :-

PRICE:

lET vSheet=1;

FOR Each vSheet in 'SHEET1$','SHEET2$','SHEET3$'

  LOAD

      BRAND_P,

      Model as PART_NO_,

      Description,

      [PRODUCT_LINE] as [PRODUCT_TYPE],

      [PRODUCT_LINE],

      Supplier,

      [Product Group],

      [LOCAL PRODUCT],

      Currency_P,

      date([Updated]) as Updated,

    Price as LIST,

      Price as LIST_PRICE

  FROM

  [$(vRAWPath)$(vFILE1)]

  (biff, embedded labels, table is $(vSheet));

Next vSheet;