Problems with Date Row/Column

[#1]

Hello.
I'm trying qlikview now and i'm having trouble with this.. I hope you guys can give me a hand, i'd be really grateful !

I'm loading data from 2 different excel files, each one has only one table/sheet

File Centros.xlsx is a file that tells us how much we can sell in each center considering how much the distribution center can send in one day, telling us how is our agenda avaiability.

File Ocupacao.xlsx tells us how much product we have in each center, and how much was already sold, considering product volume.

Ex:

Center     ID    22.12.2014                 22.12.2014C                 23.12.2014          23.12.2014C     ...

4112       12    1800                          1300                            1000                   1000                      ...

What the table above tells us : In center 4112, we have sold 1300 out of a 1800 max, in date1.

What i need:
When i click the date from table 2, i need to see both the agenda avaiability from table 1 and product quantity from table 1.

The dates are in different formats.. table 1 has a dd.mm.yyyy format mixed with dd.mm.yyyyC (witch tells us how much product we sold for day dd.mm.yyyy) and table 2 has a dd/mm/yyyy format.

Table 1:

Center     ID    Date1 Max                 Date 1 Occupied           Date2 Max          Date 2 Occupied     ...

4112       12    1800                          1300                            1000                   1000                      ...

...

Table 2:
Center     ID     Product qty av          Product sold               Date               ...

4112       12     1.200                       1000                          12/10/2012      ...

...

Problem is, the dates from table 1 are the column headers, and i can't link it to the dates on table 2 no matter how i try it..

I'll link the two tables and also the QVW file

Thx Guys..

I managed to get the dates linked using Autonumber(date), however, it's not indexing it correctly, for example, in a date range from 02/02/2014 to 09/02/2014,

it's indexing:

03/02/2014 - 1

04/02/2014 - 2

05/02/2014 - 3

06/02/2014 - 4

07/02/2014 - 5

08/02/2014 - 6

09/02/2014 - 7

02/02/2014 - 8

But the date 02/02/2014 should be number 1..

My Script so far:

Agenda:

LOAD Centro as CodCentro,

     AutoNumber(Day([Data Base]), 'count1') as Data,

     [Qtd. F.O. (TON)] as AgendaTot,

     [Qtd Agendada (TON)] as OcupAgendaTot

FROM

[..\In\Centros.xlsx]

(ooxml, embedded labels, table is Plan1)

WHERE([Descrição da F.O.] = 'FIXA PALLETIZADO');

DispProdd:

LOAD A as CodCentro,

     C as CodMaterial,

     AutoNumber(Date(Now()), 'count2') AS DataNow,

     E as ProdLivre,

     F as ProdOcupado

FROM

[..\In\Produtos.xlsx]

(ooxml, no labels, header is 1 lines, table is Plan1);

DispProdd1:

LOAD A as CodCentro,

     C as CodMaterial,

     AutoNumber(Date(Now()+1), 'count2') AS DataNow,

     G as ProdLivre,

     H as ProdOcupado

FROM

[..\In\Produtos.xlsx]

(ooxml, no labels, header is 1 lines, table is Plan1);

DispProdd2:

LOAD A as CodCentro,

     C as CodMaterial,

     AutoNumber(Date(Now()+2), 'count2') AS DataNow,

     I as ProdLivre,

     J as ProdOcupado

FROM

[..\In\Produtos.xlsx]

(ooxml, no labels, header is 1 lines, table is Plan1);

DispProdd3:

LOAD A as CodCentro,

     C as CodMaterial,

     AutoNumber(Date(Now()+3), 'count2') AS DataNow,

     K as ProdLivre,

     L as ProdOcupado

FROM

[..\In\Produtos.xlsx]

(ooxml, no labels, header is 1 lines, table is Plan1);

DispProdd4:

LOAD A as CodCentro,

     C as CodMaterial,

     AutoNumber(Date(Now()+4), 'count2') AS DataNow,

     M as ProdLivre,

     N as ProdOcupado

FROM

[..\In\Produtos.xlsx]

(ooxml, no labels, header is 1 lines, table is Plan1);

DispProdd5:

LOAD A as CodCentro,

     C as CodMaterial,

     AutoNumber(Date(Now()+5), 'count2') AS DataNow,

     O as ProdLivre,

     P as ProdOcupado

FROM

[..\In\Produtos.xlsx]

(ooxml, no labels, header is 1 lines, table is Plan1);

DispProdd6:

LOAD A as CodCentro,

     C as CodMaterial,

     AutoNumber(Date(Now()+6), 'count2') AS DataNow,

     Q as ProdLivre,

     R as ProdOcupado

FROM

[..\In\Produtos.xlsx]

(ooxml, no labels, header is 1 lines, table is Plan1);

DispProdd7:

LOAD A as CodCentro,

     C as CodMaterial,

     AutoNumber(Date(Now()+7), 'count2') AS DataNow,

     S as ProdLivre,

     T as ProdOcupado

FROM

[..\In\Produtos.xlsx]

(ooxml, no labels, header is 1 lines, table is Plan1);

[#2 · Accepted solution]

Hi Eduardo,

Have you looked at the CrossTable function?  That will make your column headings into rows.  You'll have to do some extra work to change to filter separate the date from the "max" or "occupied" text that I would put into a different column.

No now() or autonumber() function is necessary for this script.

Karl

[#3]

Thank you for the accurate and fast response Karl, that's exactly what i needed !
In case there is someone else needing help in the same way, i'll paste a post that helped me further:

Cross Table in QlikView | Learn QlikView