Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Count the number of changes

I have these two tables:

table 1:

LOAD * INLINE [

Data,State,Table 

a, 1, table 1

b, 1, table 1

c, 2, table 1   

];

table 2:

LOAD * INLINE [

Data,State,Table  

    a, 2, table 2

    b, 1, table 2

    c, 1, table 2

];

And I want, after compare table 1 to table 2, that Result Table:

Changes, Nº

State1to2, 1

State2to1,2

State1to3,0

How can I do that? Thank you!

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Count the number of changes

Maybe like attached, using this script:

table1:

LOAD * INLINE [

Data,State,Table 

a, 1, table 1

b, 1, table 1

c, 2, table 1  

];

table2:

LOAD * INLINE [

Data,State,Table 

    a, 2, table 2

    b, 1, table 2

    c, 1, table 2

];

TMP:

LOAD * INLINE [

Change, From, To

1 --> 2, 1,2

2 --> 1, 2,1

1 --> 3, 1,3

];

LEFT JOIN

LOAD Data, State as StateFrom

Resident table1

WHERE Table = 'table 1';

LEFT JOIN

LOAD Data, State as StateTo

Resident table1

WHERE Table = 'table 2';

Results:

LOAD Change,

  Count(if(From=StateFrom and To=StateTo,Data)) as Count

Resident TMP

GROUP BY Change;

DROP TABLE TMP; 

5 Replies
MVP
MVP

Re: Count the number of changes

I think you want to have 'table 2' as Table field value in your second load:

table1:

LOAD * INLINE [

Data,State,Table

a, 1, table 1

b, 1, table 1

c, 2, table 1

];

table2:

LOAD * INLINE [

Data,State,Table

    a, 2, table 2

    b, 1, table 2

    c, 1, table 2

];

Then you can create a straight table with no dimension and three expressions:

1 --> 2:

=Sum( Aggr(If(Only({<Table = {'table 1'}>} State) = 1 and Only({<Table = {'table 2'}>} State) = 2,1),  Data))

2 --> 1:

=Sum( Aggr(If(Only({<Table = {'table 1'}>} State) = 2 and Only({<Table = {'table 2'}>} State) = 1,1),  Data))

1 --> 3:

=Sum( Aggr(If(Only({<Table = {'table 1'}>} State) = 1 and Only({<Table = {'table 2'}>} State) = 3,1),  Data))

Not applicable

Re: Count the number of changes

Thank you!

That it's correct but I need the dimension "Change", how can i do that?

I want to calculate percenge of each changes.

MVP
MVP

Re: Count the number of changes

Maybe like attached, using this script:

table1:

LOAD * INLINE [

Data,State,Table 

a, 1, table 1

b, 1, table 1

c, 2, table 1  

];

table2:

LOAD * INLINE [

Data,State,Table 

    a, 2, table 2

    b, 1, table 2

    c, 1, table 2

];

TMP:

LOAD * INLINE [

Change, From, To

1 --> 2, 1,2

2 --> 1, 2,1

1 --> 3, 1,3

];

LEFT JOIN

LOAD Data, State as StateFrom

Resident table1

WHERE Table = 'table 1';

LEFT JOIN

LOAD Data, State as StateTo

Resident table1

WHERE Table = 'table 2';

Results:

LOAD Change,

  Count(if(From=StateFrom and To=StateTo,Data)) as Count

Resident TMP

GROUP BY Change;

DROP TABLE TMP; 

MVP
MVP

Re: Count the number of changes

table1:

LOAD * INLINE [

Data,State,Table

a, 1, table 1

b, 1, table 1

c, 2, table 1  

];

join 

LOAD Data,State as State1,Table as Table1   INLINE [

Data,State,Table 

    a, 2, table 2

    b, 1, table 2

    c, 1, table 2

];

left join (table1)

load *, State & '-->' & State1 as Change

Resident table1;

Not applicable

Re: Count the number of changes

Thank you!! You are a master

Community Browser