topic Re: Dinamyc load from excel an transpose in QlikView

Dinamyc load from excel an transpose

PiroGiovanni — Sat, 16 Nov 2024 21:15:02 GMT

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

Re: Dinamyc load from excel an transpose

tincholiver — Wed, 13 Mar 2019 22:52:36 GMT

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.

Re: Dinamyc load from excel an transpose

PiroGiovanni — Thu, 14 Mar 2019 05:39:54 GMT

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

Re: Dinamyc load from excel an transpose

tincholiver — Thu, 14 Mar 2019 11:59:40 GMT

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

Re: Dinamyc load from excel an transpose

PiroGiovanni — Fri, 15 Mar 2019 21:08:07 GMT

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?

Re: Dinamyc load from excel an transpose

tincholiver — Sat, 16 Mar 2019 14:29:23 GMT

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?

Re: Dinamyc load from excel an transpose

PiroGiovanni — Wed, 20 Mar 2019 08:30:23 GMT

unfortunatelly no way to change the excel 😞