Need help with scripting

[rakesh_kumar]

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

[neha_shirsath]

Hi,

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

[nitin_pawar]

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;

[antoniotiman]

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

[rakesh_kumar]

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

[Kushal_Chawda · Accepted solution]

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;

[rakesh_kumar]

Thanks a lot Kushal, Your code solved the problem.

[Kushal_Chawda]

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