Crosstable on 2 joined qvd's

[#1]

Hello,

I have a question, if you can help me, I would be grateful. I am beginner in Qlikview development.

I have 2 qvd's with below structure:

     qvd1 :            product code

                          product name                       

                          product type

                          <specific columns of qvd1>

                          KPI1

                          KPI2

                          KPI3

     qvd2:             product code

                          product name                         

                          product type

                           <specific columns of qvd2>

                          KPI4

                          KPI5

                          KPI6

                          KPI7

What I want to do: In the loading script I want to do a inner join one these 2 qvd: qvd1&qvd2 based on product code to obtain a structure like:

                          product code

                          product type

                          product name

                         <specific columns of qvd1>

                         <specific columns of qvd2>

                          KPI1

                          KPI2

                          KPI3

                          KPI4

                          KPI5

                          KPI6

                          KPI7

Then, based on this joined table, I want to make a crosstable: crosstable (kpi,data,5).

In the loading script I have:

table1:

     LOAD * from qvd1;

inner join (table 2)

       LOAD * from qvd2;

I guess that in join I will obtain something like:

                          product code

                           product name                      

                          product type

                          <specific columns of qvd1>

                          KPI1

                          KPI2

                          KPI3

                         <specific columns of qvd2>

                          KPI4

                          KPI5

                          KPI6

How can I 'put' a crosstable like on : product code, product name, product type,<specific columns of qvd1>,<specific columns of qvd2>  and KPI.

as I don't have the columns arranged? Something like: crosstable (KPI,Data,5)           

Thank you very much!!

[#2 · Accepted solution]

try according to this format

qvd1:

load

product code,

  product name,                       

  KPI1 ,

product type,

  KPI3,

KPI2,

KPI3

from qvd1.qvd

inner join

qvd2:

load

product code,

  product type,

   KPI4,

    KPI5,

   KPI6,

     product name,

        KPI7

from qvd2.qvd;

Data:

crosstable (kpi,data,3)

load *

resident qvd1;

[#3]

i,m providing the sample.

qvd1:

LOAD * INLINE [

    A, B

    1, AA

    2, CC

    3, BB

];

Inner Join

qvd2:

LOAD * INLINE [

    A, C

    1, XX

    4, YY

];

Data:

crosstable (kpi,data,1)

LOAD * Resident qvd1;

see attachement

[#4]

Hi,

U can try this also

RawData1:

load

product code,

  product name,                       

  KPI1 ,

product type,

  KPI3,

KPI2,

KPI3

from qvd1.qvd

Inner Join

RawData2:

load

product code,

  product type,

   KPI4,

    KPI5,

   KPI6,

     product name,

        KPI7

from qvd2.qvd;

BaseData: 

CrossTable(KPI,Data,3)

                          product code 

                          product type

                          product name

                          KPI1

                          KPI2

                          KPI3

                          KPI4

                          KPI5

                          KPI6

                          KPI7

Resident RawData1 ;

Hope it will work.

regards-Bika

[#5]

Hello,

Thank you for your fast response!

I did some tests on something I've got:

qvd1:

LOAD * INLINE [

    A, B, B1

    1, AA, AAA,

    2,  CC, CCC

    3,  BB, DDD

];

Inner Join

qvd2:

LOAD * INLINE [

    A, A2, C , C1

    1, 100, XX, XXX

    4, 400, YY, YYY

];

Data:

crosstable (kpi,data,1)

LOAD * Resident qvd1;

I have A2 represented as a 'KPI'-data of atributes, even I want it as an atribute column like A.

What should I put in crosstable (kpi,data,1) if I have a different number of  attribute columns in first table than in the second one (in my case 1 in first:A, 2 in second: A and A2)?

[#6]

Read the cocepts of crosstable.

Crosstable

A cross table is a common type of table featuring a matrix of values between two orthogonal lists of header data. To turn a cross table into a straight table, use a crosstable prefix.

The syntax is:

crosstable (attribute field , data field ,Qualifier)

Crosstable Wizard

The crosstable wizard is dialog driven method of creating the crosstable syntax. This dialog is opened by clicking the Crosstable button in the Options page of the File Wizard. The crosstable wizard holds the following options:

  where: 

Qualifier Fields	The number of qualifier fields that precede the fields to be transformed.
Attribute Field	The name of the new field that will contain all the fields (attribute values) to be transformed.
Data Field	The name of the new field that will contain the data of the attribute values.

[#7]

I mean my structure is QVD1: 13 columns and 6 Kpi

vs

QVD2: 7 columns and 9 Kpi

The joining columns are 2.

CrossTable on join will be Crosstable(Kpi,Data, 7)? If yes, 13-7 =6 columns will be treated as Kpi's.

How do I 'refer' them?

Thx!

[#8]

hi valentinirimia

if your problem resolved then marked it as either correct or helpful according to your question so that it might be helpful for other developer

Regards

vishwaranjan

[#9]

Thank you for the advice. I am new to community.

I read the crosstable description, vefore posting the question. But couldn't found the answer for my case posted just above.

Sincerely thank you for your help!!!!!!!