Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

Crosstable on 2 joined qvd's

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!!

Tags (3)
1 Solution

Accepted Solutions
Not applicable

Re: Crosstable on 2 joined qvd's

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;

View solution in original post

8 Replies
Not applicable

Re: Crosstable on 2 joined qvd's

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;

View solution in original post

Not applicable

Re: Crosstable on 2 joined qvd's

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

Not applicable

Re: Crosstable on 2 joined qvd's

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

Not applicable

Re: Crosstable on 2 joined qvd's

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

Not applicable

Re: Crosstable on 2 joined qvd's

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 FieldsThe number of qualifier fields that precede the fields to be transformed.
Attribute FieldThe name of the new field that will contain all the fields (attribute values) to be transformed.
Data FieldThe name of the new field that will contain the data of the attribute values.
Not applicable

Re: Crosstable on 2 joined qvd's

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!

Not applicable

Re: Crosstable on 2 joined qvd's

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

Not applicable

Re: Crosstable on 2 joined qvd's

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!!!!!!!