Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master
Master

How to make G16 display as G16-AO ?

Hi All

i Have below load script work fine for display GROUP_TYPE = 16 TO G16 :-

MapGroupType:
Mapping LOAD
[inv],
[G_TYPE_]
FROM MapGroupTypeTDS.xlsx (ooxml,embedded labels, table is [TDSS]);

sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
@307:314T AS GROUP_TYPE_,
// Due to some of invoice enter the wrong GROUP_TYPE
'G'&ApplyMap('MapGroupType',@43:48T,@307:314T) AS GROUP_TYPE,
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\Q_DOD_2018.txt (ansi, fix, no labels, header is 0, record is line);

May i know how to edit the above script , so that it will make G16 display G16-AO ?

May i know how to do it with out using partial reload ?

Paul Yeo

1 Solution

Accepted Solutions
Highlighted
Master
Master

Hi All

Below is the load script using partial reload working fine :-

MapGroupType:
Mapping LOAD
[inv],
[G_TYPE_]
FROM MapGroupTypeTDS.xlsx (ooxml,embedded labels, table is [TDSS]);

sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
//@307:314T AS GROUP_TYPE,
// Due to some of invoice enter the wrong GROUP_TYPE
'G'&ApplyMap('MapGroupType',@43:48T,@307:314T) AS GROUP_TYPE_,
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\Q_DOD_2018.txt (ansi, fix, no labels, header is 0, record is line);

// may i know why i do not adding ADD before LOAD GROUP_TYPE_ , SOURCE , yet it still working ?

 

sales_G:

LOAD GROUP_TYPE_ , SOURCE ,


if (GROUP_TYPE_='G1','G01-GT',
if (GROUP_TYPE_='G2','G02-AP',
if (GROUP_TYPE_='G3','G03-SL',
if (GROUP_TYPE_='G4','G04-CL',
if (GROUP_TYPE_='G5','G05-RW',
if (GROUP_TYPE_='G6','G06-PG',
if (GROUP_TYPE_='G7','G07-BP',
if (GROUP_TYPE_='G8','G08-KL',
if (GROUP_TYPE_='G9','G09-TH',
if (GROUP_TYPE_='G10','G10',
if (GROUP_TYPE_='G12','G12-EX',
if (GROUP_TYPE_='G13','G13-ID',
if (GROUP_TYPE_='G14','G12-EX',
if (GROUP_TYPE_='G16','G16-AO',
)))))))))))))) as [GROUP_TYPE]


resident sales;

 

View solution in original post

6 Replies
Highlighted
Partner
Partner

What about updating your source excel used in the mapping such that where [G_TYPE_] todays says '16' you write '16-AO'

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted

If i get correct input from you? Perhaps this way

SubField('G'&ApplyMap('MapGroupType',@43:48T,@307:314T),'-', 1) AS GROUP_TYPE,

Or

'G'&ApplyMap('MapGroupType',@43:48T,@307:314T)&'-AO' AS GROUP_TYPE,

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Master
Master

Hi Sir

The Mapping is for recode some invoice number to 16. As user wrongly enter to 2. Therefore I am not able to enter 16-AO

Any way thank you for your sharing. So I have no choice , I use partial reload to solve it.

Paul Yeo
Highlighted
Master
Master

Hi Sir

My field have 1 to 16 . If I follow your instruction, then all Group will have same name.

Any way I manage to solve it using partial reload.
Highlighted

Fantastic !! Please mark your self as correct answer and post exact script you have written so that It helps for others ..

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Master
Master

Hi All

Below is the load script using partial reload working fine :-

MapGroupType:
Mapping LOAD
[inv],
[G_TYPE_]
FROM MapGroupTypeTDS.xlsx (ooxml,embedded labels, table is [TDSS]);

sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
//@307:314T AS GROUP_TYPE,
// Due to some of invoice enter the wrong GROUP_TYPE
'G'&ApplyMap('MapGroupType',@43:48T,@307:314T) AS GROUP_TYPE_,
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\Q_DOD_2018.txt (ansi, fix, no labels, header is 0, record is line);

// may i know why i do not adding ADD before LOAD GROUP_TYPE_ , SOURCE , yet it still working ?

 

sales_G:

LOAD GROUP_TYPE_ , SOURCE ,


if (GROUP_TYPE_='G1','G01-GT',
if (GROUP_TYPE_='G2','G02-AP',
if (GROUP_TYPE_='G3','G03-SL',
if (GROUP_TYPE_='G4','G04-CL',
if (GROUP_TYPE_='G5','G05-RW',
if (GROUP_TYPE_='G6','G06-PG',
if (GROUP_TYPE_='G7','G07-BP',
if (GROUP_TYPE_='G8','G08-KL',
if (GROUP_TYPE_='G9','G09-TH',
if (GROUP_TYPE_='G10','G10',
if (GROUP_TYPE_='G12','G12-EX',
if (GROUP_TYPE_='G13','G13-ID',
if (GROUP_TYPE_='G14','G12-EX',
if (GROUP_TYPE_='G16','G16-AO',
)))))))))))))) as [GROUP_TYPE]


resident sales;

 

View solution in original post