topic Cross table in QlikView

Cross table

ajsjoshua — Mon, 11 Sep 2017 06:34:33 GMT

Hi all,

I want to convert the following columns vertically as Branch

0854 - GXWFI

0855 - GXOAS

0857 - GXBU1

0859 - GXGAZ

0860 - GXFRA

0861 - GXHDB

0862 - GXPLD

0863 - GXNHD

0864 - GXHMN

0865 - GXMAC

0866 - GXARM

0868 - GXTCM

0869 - GXFRD

0870 - GXNTT

0872 - GXUAQ

0873 - GXMZY

0874 - GXBRJ

0877 - GXQOZ

0878 - GXDIA

and following columns vertically as hypermarket

Ajman

Deira

Al Saqr ADH

RAK Manar

Marina Mall

Jimmy Mall

Sharjah

Shindaga

Century Mall

Mall of Emirates

Al Ain Bawadi

RAK Zaith 2

Dalma Store AUH

Mirdiff

Baniyas

Fuj Cent Mall

pls find the attached excel.

Regards,

Joshua

Re: Cross table

Anil_Babu_Samineni — Mon, 11 Sep 2017 06:41:50 GMT

Try this? PFA

USE JOIN for 2 cross tables?

Re: Cross table

ajsjoshua — Mon, 11 Sep 2017 06:54:21 GMT

Dear Anil,

I am getting this error

Regards,

Joshua.

Re: Cross table

its_anandrjs — Mon, 11 Sep 2017 06:55:22 GMT

Use Cross table load with the wizard way and try this way and load the table two times

//When create this table select only Branch columns

Table1:

CrossTable(Branch, Data, 20)

LOAD

...

//When create this table select only Hypermarket columns

Table2:

CrossTable(hypermarket, Data, 20)

LOAD

...

Re: Cross table

Anil_Babu_Samineni — Mon, 11 Sep 2017 06:57:30 GMT

May be remove JOIN and check

Re: Cross table

its_anandrjs — Mon, 11 Sep 2017 06:58:46 GMT

After loading this tables use resident load of the tables.

Table1:

CrossTable(Branch, Data, 20)

LOAD

...

//When create this table select only Hypermarket columns

Table2:

CrossTable(hypermarket, Data, 20)

LOAD

...

///////////////////////////

Noconcatenate

Final:

Load * , 'Branch Table' as TableFlag Resident Table1;

Concatenate(Final)

Load * , 'Hypermarket Table' as TableFlag Resident Table2;

Drop Tables Table1,Table2;

Re: Cross table

ajsjoshua — Mon, 11 Sep 2017 07:26:29 GMT

Dear Anil,

All hypermarkets are also coming under Branch column I want only from 0854-GXWFI to 0878-GXDIA in branch column .

In hypermarket column i want only from Ajman to Fuj Cent Mall

Regards,

Joshua.

Re: Cross table

Anil_Babu_Samineni — Mon, 11 Sep 2017 07:29:34 GMT

As i stated over end up the script, There is some Remove(Col, Pos(Top, 36)), that means i've removed all those which is unnecessary, Will you do the same while transformation

Re: Cross table

ajsjoshua — Mon, 11 Sep 2017 07:43:46 GMT

Dear Anil,

Yes bro but the columns still exists.

Regards,

Joshua.

Re: Cross table

Anil_Babu_Samineni — Mon, 11 Sep 2017 07:53:57 GMT

Try this?

Re: Cross table

ajsjoshua — Fri, 15 Sep 2017 06:28:24 GMT

Dear Anil,

While executing it i am getting only express total quantity in data but i also want the express total sales data.

Regards,

Joshua.