Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

How to combine 3 similar set of script into 1 ?

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$);

1 Solution

Accepted Solutions
paulyeo11
Master
Master
Author

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;

View solution in original post

7 Replies
paulyeo11
Master
Master
Author

Enclosed my Raw Data file from excel.

paulyeo11
Master
Master
Author

Finally my QV doc.

Sokkorn
Master
Master

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
Master
Master
Author

Hi Sok

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

error from.png

paulyeo11
Master
Master
Author

Ho Sok

vSheet how should i declare ?

Paul

Sokkorn
Master
Master

Hi Paul,

No need to declare vSheet. See my attached image

Untitled.png

Regards,

Sokkorn

paulyeo11
Master
Master
Author

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;