Issues with data calculation against same field ID

[Anonymous]

Hi Can anyone help me,

I have a data file which presents the same data field ID over a period of 5 months; however each row of this field presents different data fields against the same data field ID.

For example is have the following:

100387	96.0%	Feb-17
100387	81.7%	Mar-17
100387	84.4%	Apr-17
100387	84.8%	May-17
100387	69.8%	Jun-17

Each 100387 is the same product code, and what I want to do is be able to input to QV each entry of 100387 with the percentages against each month, but I dont want QV to assume there are 5 x 100387's; i.e., I dont want QV to assume there are five (5) 100387 products.

Presently QV is assuming there are 5 x 100387, with 5 seperate %'s, rather than assuming that there is 1 x 100387 calculating %'s over 5 months.

I have a data file in excess of 25,000 entries; however there should only be around 5,000 products being processed by QV. Presetnly QV is takng every product code and then adding the same product code again as a count for a following months % data against the same product code.

Can anyone help?

[sunny_talwar]

Oh ya, this was just me loading the data for test purposes... you might just need to do something like this

Table:

LOAD *,

     Date(MonthStart(Date#(Month, 'MMM-YY')), 'MMM-YY') as MonthYear

LOAD ....

FROM someExcelfile.xlsx

();

What I did was called inline load.

[Anonymous]

Sorry to be such a pain; therefore do I need to state exactly where the inline load needs to be taken from the Test File.xls. In the case of this test file the Full Product ID is within column 'C' of the excel file?

You were correct beforehand there was only 6 distinct Product IDs and not 12 as I had implied.

Chris

[sunny_talwar]

Please find attached the Excel loaded script

Table:

LOAD *,

  Date(MonthStart(Month), 'MMM-YY') as MonthYear;

LOAD Type,

     [Minor Product ID],

     [Full Product ID],

     [Asset ID],

     Region,

     [Road / Geog],

     [Road MP],

     [Device Status],

     [STI Status],

     [Site Description],

     [Known Issues],

     Availability,

     Responsibility,

     Month

FROM

[264727.xlsx]

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

I'd fiddled with it just before you responded, and got the test file to load. However your confirmation above has confirmed it was the correct thing to do. I thank you for the support, you didnt have to do it but you did, so thanks again. I may come back if I find that the full load of the complete file doesnt go I'd planned but hopefully it will. Once again thanks for your help. Chris

[sunny_talwar]

No problem Chris, I am glad I am able to steer you in the right direction.

Best,

Sunny