topic Re: Need help with scripting in QlikView

Need help with scripting

rakesh_kumar — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Need help with scripting

neha_shirsath — Tue, 01 Aug 2017 06:46:01 GMT

Hi,

Please find the attached file. Hope this is what you are expecting.

Re: Need help with scripting

nitin_pawar — Tue, 01 Aug 2017 07:41:04 GMT

Please find below script.

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;

Re: Need help with scripting

antoniotiman — Tue, 01 Aug 2017 08:05:43 GMT

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

Re: Need help with scripting

rakesh_kumar — Tue, 01 Aug 2017 12:59:51 GMT

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

Re: Need help with scripting

Kushal_Chawda — Tue, 01 Aug 2017 15:02:26 GMT

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;

Re: Need help with scripting

rakesh_kumar — Tue, 01 Aug 2017 19:13:38 GMT

Thanks a lot Kushal, Your code solved the problem.

Re: Need help with scripting

Kushal_Chawda — Wed, 02 Aug 2017 06:49:41 GMT

there was one mistake in code, I have highlighted in bold