Problems with Date Row/Column

Sun, 02 Feb 2014 19:49:52 GMT

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);

Re: Problems with Date Row/Column

Sun, 02 Feb 2014 20:03:13 GMT

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

Re: Problems with Date Row/Column

Mon, 03 Feb 2014 12:38:12 GMT

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

topic Problems with Date Row/Column in QlikView

Problems with Date Row/Column

Re: Problems with Date Row/Column

Re: Problems with Date Row/Column