Loop and Variable Issue

Hi Knime Enthusisatic !!
Hope you are doing well.

I want your help for below problem.
First let me explain what I am working on / my objective:

I have list of Delivery center against it i have different product as column header and row have value which means quantity.
For example :

Delivery Center KAJU KATRI 250 GRAMS ( કાજુ કતરી ૨૫૦ ગ્રામ ) KAJU KATRI 500 GRAMS ( કાજુ કતરી ૫૦૦ ગ્રામ ) KAJU KATRI 1 kg . ( કાજુ કતરી ૧ કિલો ) KESAR KAJU KATRI 250 GRAMS ( કેસર કાજુ કતરી ૨૫૦ ગ્રામ )
Andheri (E)
Andheri (E) 2
Andheri (E) 2
Bhayandar 2
Andheri (E)
Bhayandar
Bhayandar 1
Bhayandar
Bhayandar
Bhayandar
Bhayandar 3
Bhayandar 5
Bhayandar 1

Like this I have more than 25 Delivery location and more than 60 products, here I have given example for just 2 Delivery location and 4 products and wherever empty value is there means 0 quanity of order.

Now, I am trying to prepare bill for each location wise where i will do summation of total order for one location and each product. like below:
For Andheri :

Delivery Center KAJU KATRI 250 GRAMS ( કાજુ કતરી ૨૫૦ ગ્રામ ) KAJU KATRI 500 GRAMS ( કાજુ કતરી ૫૦૦ ગ્રામ ) KAJU KATRI 1 kg . ( કાજુ કતરી ૧ કિલો ) KESAR KAJU KATRI 250 GRAMS ( કેસર કાજુ કતરી ૨૫૦ ગ્રામ )
Total 0 4 0 0

For Bhayandar:

Delivery Center KAJU KATRI 250 GRAMS ( કાજુ કતરી ૨૫૦ ગ્રામ ) KAJU KATRI 500 GRAMS ( કાજુ કતરી ૫૦૦ ગ્રામ ) KAJU KATRI 1 kg . ( કાજુ કતરી ૧ કિલો ) KESAR KAJU KATRI 250 GRAMS ( કેસર કાજુ કતરી ૨૫૦ ગ્રામ )
Total 8 0 1 3

Now I am doing transpose so that i get all details as per my requirement:

Product Total Quantity (Bhayandar) Total Quantity (Andheri)
KAJU KATRI 250 GRAMS ( કાજુ કતરી ૨૫૦ ગ્રામ ) 8 0
KAJU KATRI 500 GRAMS ( કાજુ કતરી ૫૦૦ ગ્રામ ) 0 4
KAJU KATRI 1 kg . ( કાજુ કતરી ૧ કિલો ) 1 0
KESAR KAJU KATRI 250 GRAMS ( કેસર કાજુ કતરી ૨૫૦ ગ્રામ ) 3 0

After that My requirement is to bring Rate of each product and multiply with order quanity. I have separate list of price against product that i will bring using joiner.

like below ( this is just for one location):

Product Rate Total Quantity (Bhayandar) Amount
KAJU KATRI 250 GRAMS ( કાજુ કતરી ૨૫૦ ગ્રામ ) 230 8 1840
KAJU KATRI 500 GRAMS ( કાજુ કતરી ૫૦૦ ગ્રામ ) 460 0 0
KAJU KATRI 1 kg . ( કાજુ કતરી ૧ કિલો ) 920 1 920
KESAR KAJU KATRI 250 GRAMS ( કેસર કાજુ કતરી ૨૫૦ ગ્રામ ) 250 3 750
Grand Total 3510

Now my only objective is to export every delivery location in one excel but different sheet.

My workflow looks like below:

Output like below:

Now help me for making respective changes…

  1. I want to put each output in excel but separate sheet
  2. If iteration can get which delivery location is this it would be helpful.
  3. KNIME is not recognising Gujarati language, please help for that also.

Thanks in advance
KNIME_project12.knwf (108.2 KB)
Edit: I have uploaded my workflow as well

@ravi13 you can handle the first iteration different and overwrite the Excel file and in the following loops append Excel file.

On my Mac the UTF-8 encoding seems to work for the Gujarati language. You might have to switch to the classic UI to get to the settings.

3 Likes

Hi
Thanks for the workflow… but could you please help with where i can get excel output or how can i change setting for output to my local system.

The path is being set in the “Create File/Folder Variables” node like this:

You can adapt that to the folders you want.

https://docs.knime.com/latest/analytics_platform_file_handling_guide/index.html#introduction

2 Likes