Dinamyc load from excel an transpose

[PiroGiovanni]

Hi All,

I've this issue:

Excel sheet with some columns that contains basic information about activities and several columns with dates that contains activity planning

Ex.

columns --> Activity Type; Activity description; Stard date; End Date; 01/01/2019; 01/02/2019; 01/03/2019 ....

values --> ACT001; Car Production; 01/01/2019; 01/20/2019; 0; 1; 2; ....

I would like to upload those information into a tab that will have

Activity Type; Activity description; Stard date; End Date; Reference date; Calue

ACT001; Car Production; 01/01/2019; 01/20/2019; 01/01/2019; 0

ACT001; Car Production; 01/01/2019; 01/20/2019; 01/02/2019; 1

ACT001; Car Production; 01/01/2019; 01/20/2019; 01/03/2019; 2

Basically I want to dinamicalli read all the columns with dates and transpose them into row

I hope I was clear in the request

[tincholiver]

Hello,
load the script below, the result is:

Directory;
Table:
CrossTable([Ref date], Value, 4)
LOAD [Act Type],
[Act descr],
Start,
end,
[43466],
[43467],
[43468],
[43469],
[43470],
[43471],
[43472],
[43473],
[43474],
[43475],
[43476],
[43477],
[43478]
FROM example.xlsx (ooxml, embedded labels, table is INPUT);
STORE Table into Example.qvd(qvd);DROP Table Table;

Directory;
LOAD
[Act Type],
[Act descr],
Date(Start,'DD/MM/YYYY') as Start,
Date(end,'DD/MM/YYYY') as end,
Date(num#([Ref date]),'DD/MM/YYYY') as [Ref date] ,
Value
FROM Example.qvd(qvd);

tell me if the solution served you.

[PiroGiovanni]

Hi this works but is not what I need because with this script I have fixed dates
[43466],
[43467],
[43468],
[43469],
[43470],
[43471],
[43472],
[43473],
[43474],
[43475],
[43476],
[43477],
[43478]
And I need to have them dynamic because in one file I can have those dates in another I can have other and I don't know it in advance

[tincholiver]

I understand, and you do not know how many columns of width that file will have?

[PiroGiovanni]

No this is the issue, it may be 10 as it may be 100

I need to read the full sheet check which columns are filled and after apply the logic

Do you have any idea?

In case instead I'll be able to fix to 100 or something like that how can I solve the issue?

[tincholiver]

Hello Piro, I was looking at the case.
The drawback I see is that, just as the width of the table is variable, the names of the date fields will also be variable. In this way, taking the start value and the end can change between one recharge and another and thus there may be inconsistencies in the information.
Is there a way you can change the way you fill out the data on the excel sheet or is it a file that you take from a database?

[PiroGiovanni]

unfortunatelly no way to change the excel 😞