Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lakshmanvepuri
Creator
Creator

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

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:

lakshmanvepuri_0-1610818207611.png

2. I defined as Item_Details

LOAD  INLINE [FUNCTION, ITEM_ID, ITEM_NAME,

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

lakshmanvepuri_1-1610818356026.png

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

lakshmanvepuri_2-1610818459841.png

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

lakshmanvepuri_3-1610818549835.png

 

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

 

Thanks in Advance

LK Vepuri

 

 

 

 

 

1 Solution

Accepted Solutions
hugoqliksense
Contributor II
Contributor II

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

View solution in original post

3 Replies
hugoqliksense
Contributor II
Contributor II

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
Contributor II
Contributor II

It's OK

hugoqliksense
Contributor II
Contributor II

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;