Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
Please find the attached sample Excel file with raw data like this Table:
and I would like to get the output Table like below only using the Scripting (Not front end):
Here RULE_NAME concatenates using "::" where the Dimension_Name = INT & COV have the same SYSTEM_NAME and ACCEPTED_VALUES and REJECTED_VALUES are summed up for the respective records.
Please can some one help with this?
Kind Regards,
RK
Data:
LOAD ID,
RULE_NAME,
DIMENSION_NAME,
SYSTEM_NAME,
ACCEPTED_VALUES,
REJECTED_VALUES
FROM
[QV Sample Data v1.xlsx]
(ooxml, embedded labels, table is Data);
Min:
LOAD FirstSortedValue(DISTINCT DIMENSION_NAME,ID) as MinID_NAME
Resident Data
where match(DIMENSION_NAME,'COV','INT');
let vName1 = Peek('MinID_NAME',0,'Min');
let vName2= if('$(vName1)'='INT','COV','INT');
New:
LOAD ID as ID1,
'$(vName2)' as NAME1
Resident Data
where match(DIMENSION_NAME,'$(vName1)');
Join(New)
LOAD ID as ID2,
'$(vName1)' as NAME2
Resident Data
where match(DIMENSION_NAME,'$(vName2)');
Left Join(New)
LOAD ID as ID1,
DIMENSION_NAME as NAME2,
SYSTEM_NAME as SYS_NAME2,
ACCEPTED_VALUES as ACCEPTED_VALUES2,
REJECTED_VALUES as REJECTED_VALUES2
Resident Data;
Left Join(New)
LOAD ID as ID2,
DIMENSION_NAME as NAME1,
SYSTEM_NAME as SYS_NAME1,
ACCEPTED_VALUES as ACCEPTED_VALUES1,
REJECTED_VALUES as REJECTED_VALUES1
Resident Data;
Final:
NoConcatenate
LOAD ID1 as ID,
'ID'&ID1&'::'&'ID'&ID2 as RULE_NAME,
RangeSum(ACCEPTED_VALUES1,ACCEPTED_VALUES2) as ACCEPTED_VALUES,
RangeSum(REJECTED_VALUES1,REJECTED_VALUES2) as REJECTED_VALUES
Resident New
where SYS_NAME1=SYS_NAME2;
DROP Table New;
Inner Join(Final)
LOAD ID ,
DIMENSION_NAME,
SYSTEM_NAME
Resident Data;
Concatenate(Final)
LOAD ID,
RULE_NAME,
DIMENSION_NAME,
SYSTEM_NAME,
ACCEPTED_VALUES,
REJECTED_VALUES
Resident Data
where not match(DIMENSION_NAME,'COV','INT');
DROP Table Data;
Hi,
Please find the attached file. Hope this is what you are expecting.
Please find below script.
T:
LOAD ID,
RULE_NAME,
DIMENSION_NAME,
SYSTEM_NAME,
ACCEPTED_VALUES,
REJECTED_VALUES
FROM
[QV Sample Data.xlsx]
(ooxml, embedded labels, table is Data) Where not WildMatch(DIMENSION_NAME,'INT','COV') ;
Concatenate
LOAD SubField( concat(ID,'/'),'/',1) as ID,
Concat(RULE_NAME,'::') as RULE_NAME,
SubField( Concat(DIMENSION_NAME,'/'),'/',2) as DIMENSION_NAME,
SYSTEM_NAME,
sum(ACCEPTED_VALUES) as ACCEPTED_VALUES ,
sum(REJECTED_VALUES) as REJECTED_VALUES
FROM
[QV Sample Data.xlsx]
(ooxml, embedded labels, table is Data) Where WildMatch(DIMENSION_NAME,'INT','COV') Group by SYSTEM_NAME;
Hi,
maybe this
Temp:
LOAD ID,
RULE_NAME,
DIMENSION_NAME,
SYSTEM_NAME,
ACCEPTED_VALUES,
REJECTED_VALUES
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1316905-289825/QV%20Sample%20Data.xlsx"
(ooxml, embedded labels, table is Data);
NoConcatenate
LOAD Concat(RULE_NAME,';;') as RULE_NAME,MinString(ID) as ID,MaxString(DIMENSION_NAME) as DIMENSION_NAME,
SYSTEM_NAME,Sum(ACCEPTED_VALUES) as ACCEPTED_VALUES,Sum(REJECTED_VALUES) as REJECTED_VALUES
Resident Temp Group By SYSTEM_NAME;
Drop Table Temp;
Regards,
Antonio
Hi Guys,
Thanks a lot for the replies and the solutions are great.
However I just checked this code against my actual requirement and realised that I have missed adding some more data earlier to make it clear and also I am expecting slightly different output. Sorry for missing on this earlier. Please find the attached updated sample data and please could you help me with the script that can produce an output Table as below?
Many Thanks,
RK
Data:
LOAD ID,
RULE_NAME,
DIMENSION_NAME,
SYSTEM_NAME,
ACCEPTED_VALUES,
REJECTED_VALUES
FROM
[QV Sample Data v1.xlsx]
(ooxml, embedded labels, table is Data);
Min:
LOAD FirstSortedValue(DISTINCT DIMENSION_NAME,ID) as MinID_NAME
Resident Data
where match(DIMENSION_NAME,'COV','INT');
let vName1 = Peek('MinID_NAME',0,'Min');
let vName2= if('$(vName1)'='INT','COV','INT');
New:
LOAD ID as ID1,
'$(vName2)' as NAME1
Resident Data
where match(DIMENSION_NAME,'$(vName1)');
Join(New)
LOAD ID as ID2,
'$(vName1)' as NAME2
Resident Data
where match(DIMENSION_NAME,'$(vName2)');
Left Join(New)
LOAD ID as ID1,
DIMENSION_NAME as NAME2,
SYSTEM_NAME as SYS_NAME2,
ACCEPTED_VALUES as ACCEPTED_VALUES2,
REJECTED_VALUES as REJECTED_VALUES2
Resident Data;
Left Join(New)
LOAD ID as ID2,
DIMENSION_NAME as NAME1,
SYSTEM_NAME as SYS_NAME1,
ACCEPTED_VALUES as ACCEPTED_VALUES1,
REJECTED_VALUES as REJECTED_VALUES1
Resident Data;
Final:
NoConcatenate
LOAD ID1 as ID,
'ID'&ID1&'::'&'ID'&ID2 as RULE_NAME,
RangeSum(ACCEPTED_VALUES1,ACCEPTED_VALUES2) as ACCEPTED_VALUES,
RangeSum(REJECTED_VALUES1,REJECTED_VALUES2) as REJECTED_VALUES
Resident New
where SYS_NAME1=SYS_NAME2;
DROP Table New;
Inner Join(Final)
LOAD ID ,
DIMENSION_NAME,
SYSTEM_NAME
Resident Data;
Concatenate(Final)
LOAD ID,
RULE_NAME,
DIMENSION_NAME,
SYSTEM_NAME,
ACCEPTED_VALUES,
REJECTED_VALUES
Resident Data
where not match(DIMENSION_NAME,'COV','INT');
DROP Table Data;
Thanks a lot Kushal, Your code solved the problem.
there was one mistake in code, I have highlighted in bold