Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How do we use cross table on two fields.
I have a data below.
The output should be
How to achieve this scenario using cross table?
Please check the attached app for sample data.
Thanks,
@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 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;
Thanks for providing the solution.