Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bharathkamath28
Contributor III
Contributor III

cross table on two fields in Qliksense

Hi @tresesco  @sunny_talwar 

How do we use cross table on two fields.

I have a data below.

bharathkamath28_0-1601995784162.png

The output should be 

bharathkamath28_1-1601995824589.png

How to achieve this scenario using cross table?

Please check the attached app for sample data.

Thanks,

 

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@bharathkamath28  In case you don't have number reference in your column name like Date1..Date2 then try below

Table1:
CrossTable(Name,Values)
Load * inline [
ID,Value3,Value2,Value1,Date3,Date2,Date
1,10,20,30,01/01/2020,01/02/2020,01/03/2020
2,15,30,45,01/01/2019,01/02/2019,01/03/2019
];

T2:
NoConcatenate
Load *,
    if(WildMatch(Name,'*Date*') = WildMatch(previous(Name),'*Date*') or
    WildMatch(Name,'*Value*') = WildMatch(previous(Name),'*Value*'),
    rangesum(Peek('Key'),1),1) as Key
Resident Table1;

Drop Table Table1;

Final:
Load ID,
     Values,
     Key
Resident T2
where Name like '*Value*';

Join(Final)
Load ID,
     Values as Date,
     Key
Resident T2
where Name like '*Date*';

Drop Table T2;

Drop Fields Key;

 

View solution in original post

3 Replies
Kushal_Chawda

@bharathkamath28  In case you don't have number reference in your column name like Date1..Date2 then try below

Table1:
CrossTable(Name,Values)
Load * inline [
ID,Value3,Value2,Value1,Date3,Date2,Date
1,10,20,30,01/01/2020,01/02/2020,01/03/2020
2,15,30,45,01/01/2019,01/02/2019,01/03/2019
];

T2:
NoConcatenate
Load *,
    if(WildMatch(Name,'*Date*') = WildMatch(previous(Name),'*Date*') or
    WildMatch(Name,'*Value*') = WildMatch(previous(Name),'*Value*'),
    rangesum(Peek('Key'),1),1) as Key
Resident Table1;

Drop Table Table1;

Final:
Load ID,
     Values,
     Key
Resident T2
where Name like '*Value*';

Join(Final)
Load ID,
     Values as Date,
     Key
Resident T2
where Name like '*Date*';

Drop Table T2;

Drop Fields Key;

 

bharathkamath28
Contributor III
Contributor III
Author

@Kushal_Chawda 

Thanks for providing the solution.