Automation of Excel file in Qlikview Script applying Business Logic to CSV Generation

[lakshmanvepuri]

Hi All,

Please let me know how to automate the below excel in Qlikview Script.

Based on this Will be having large volume of details will be given as input and to convert to Excel as desired by the Business.

Need Help in creating the automation for this script.

1. Input Data:

2. I defined as Item_Details

LOAD  INLINE [FUNCTION, ITEM_ID, ITEM_NAME,

TRANSPORT, 4011, CAR
TRANSPORT, 4012, BUS
TRANSPORT, 4013, LORRY ]

3.  Based on 1 and 2 ,  Weight should be taken as distinct to get the below result.( Let me know how to generate this ) 

4. Based on S.No 1 and S.No 3 , Output should be generated as below.

Please let me know how to generate S.No 3 and S.No 4 Using the Script.

Thanks in Advance

LK Vepuri

[hugoqliksense · Accepted solution]

Nr3 could be like this:

Spoiler

SET calculateNr=if($1=$2, $3+1, 1)
;

tmp:

LOAD distinct
'CAR' as ITEM_NAME,
CAR as WEIGHT
FROM [lib://OneDrive/SampleFile.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Unitof Measure])
Where IsNum(CAR)
;

LOAD distinct
'BUS' as ITEM_NAME,
BUS as WEIGHT
FROM [lib://OneDrive/SampleFile.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Unitof Measure])
Where IsNum(BUS)
;

LOAD distinct
'LORRY' as ITEM_NAME,
LORRY as WEIGHT
FROM [lib://OneDrive/SampleFile.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Unitof Measure])
Where IsNum(LORRY)
;

tmp2:
Load *,
$(calculateNr(ITEM_NAME,Peek('ITEM_NAME'),Peek('nr'))) As nr
Resident tmp;
drop table tmp;

[ml_id]:
MAPPING LOAD *
INLINE [
ITEM_NAME,ITEM_ID,
CAR, 4011
BUS, 4012
LORRY, 4013
]
;

tmp3:
LOAD *,
APPLYMAP('ml_id',ITEM_NAME, null()) &'-'& nr AS ITEM_ID_NEW
RESIDENT tmp2;
drop table tmp2;
drop field nr;

Then it hurts, it's time to sleep, but I think it's not difficult to hang the rest with mapping.

https://t.me/qlikbi_chat/36155

[hugoqliksense]

It's OK

[hugoqliksense]

QS load script:

Spoiler

out1:
LOAD
Model,
Name,
Fuel,
"Total",
Output,
"Final Score",
'CAR|' & CAR as car#weight,
'BUS|' & BUS as bus#weight,
'LORRY|' & LORRY as lorry#weight
FROM [lib://OneDrive/SampleFile.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Unitof Measure])
Where not IsNull(Model);

tmp:
LOAD distinct
'CAR' as ITEM_NAME,
CAR as WEIGHT
FROM [lib://OneDrive/SampleFile.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Unitof Measure])
Where IsNum(CAR);

LOAD distinct
'BUS' as ITEM_NAME,
BUS as WEIGHT
FROM [lib://OneDrive/SampleFile.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Unitof Measure])
Where IsNum(BUS);

LOAD distinct
'LORRY' as ITEM_NAME,
LORRY as WEIGHT
FROM [lib://OneDrive/SampleFile.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Unitof Measure])
Where IsNum(LORRY);

tmp2:
Load *,
if(ITEM_NAME=Peek('ITEM_NAME'), Peek('nr')+1, 1) As nr
Resident tmp;
drop table tmp;

[ml_id]:
MAPPING LOAD *
INLINE [
ITEM_NAME,ITEM_ID,
CAR, 4011
BUS, 4012
LORRY, 4013
];

tmp3:
LOAD *,
APPLYMAP('ml_id',ITEM_NAME, null()) &'-'& nr AS ITEM_ID_NEW,
ITEM_NAME &'|'& WEIGHT as key
RESIDENT tmp2;
drop table tmp2;

[ml_itid]:
MAPPING
LOAD
key,ITEM_ID_NEW
RESIDENT tmp3;

[ml_itnm]:
MAPPING LOAD
key,ITEM_NAME
RESIDENT tmp3;

DROP TABLE tmp3;

out:
LOAD *,
APPLYMAP('ml_itid',car#weight, null()) AS ITEM_ID_NEW,
APPLYMAP('ml_itnm',car#weight, null()) AS ITEM_NAME
RESIDENT out1;

LOAD *,
APPLYMAP('ml_itid',bus#weight, null()) AS ITEM_ID_NEW,
APPLYMAP('ml_itnm',bus#weight, null()) AS ITEM_NAME
RESIDENT out1;

LOAD *,
APPLYMAP('ml_itid',lorry#weight, null()) AS ITEM_ID_NEW,
APPLYMAP('ml_itnm',lorry#weight, null()) AS ITEM_NAME
RESIDENT out1;

drop table out1;
drop fields car#weight,bus#weight,lorry#weight;