Loading multiple headers in QlikView

[Nisha_Pra]

Hi All,

I have three different headers based on language and wanted to know how I can load this data in QV along with respective data for these three headers.

If you look at the sample below:

Row 1, 3, 5 are headers. Row 2,4,6 are respective data for these three headers. Now on the UI I want to create three report based on different header/language. Only identifier we have for three headers - LangaugeFlag. 

Date	Language	Language Flag	Issue
16-05-2021	English	English	A
Date	Language	CHHeader	Issue
16-05-2021	Chinese	Chinese	B
Date	Language	JPHeader	Issue
16-05-2021	Japanese	Japanese	C

Sample Output:

Output 1: English

Date	Language	LanguageFlag	Issue
16-05-2021	English	English	A

Output 2: Chinese

Date	Language	CHHeader	Issue
16-05-2021	Chinese	Chinese	B

Output 3: Japanese

Date	Language	JPHeader	Issue
16-05-2021	Japanese	Japanese	C

Please let me know how this can be achieved in the backend script. 

Note: have huge volume of data and loading the table from database. Only way to differentiate three headers based on Language Flag column

Can someone help?

[vinieme12]

why not just remove header rows?

temp:
load * inline
[
Date,Language,Language Flag,Issue
16-05-2021,English,English,A
Date,Language,CHHeader,Issue
16-05-2021,Chinese,Chinese,B
Date,Language,JPHeader,Issue
16-05-2021,Japanese,Japanese,C
]
Where Date<>'Date';

[Nisha_Pra]

Hi Vineeth, I want to have three headers. based on the language I should show the different headers in the report from same data model.

Thanks,

Nisha

[vinieme12]

If this exactly how your data is, i don't think you need to do any further transformations in script

Doing the below will return

temp:
load * inline
[
Date,Language,Language Flag,Issue
16-05-2021,English,English,A
Date,Language,CHHeader,Issue
16-05-2021,Chinese,Chinese,B
Date,Language,JPHeader,Issue
16-05-2021,Japanese,Japanese,C
]
Where Date<>'Date';

OutputTable will be as below

Date,Language,Language Flag,Issue
16-05-2021,English,English,A
16-05-2021,Chinese,Chinese,B
16-05-2021,Japanese,Japanese,C

Then in charts Use

Dimensions

Date, Language

Measure

= English Issues Count =  count({<Language={'English'}>}Issue)

= Chinese Issues Count =  count({<Language={'Chinese'}>}Issue)

= Japanese Issues Count =  count({<Language={'Japanese'}>}Issue)

[Nisha_Pra]

Hi Vineeth,

I have lot of columns in the data set which will show the column name in Chinese language for Chinese data, in Japanese language for Japan data and for other languages column name will be English. Thats why I have three different headers in data set for multiple languages. On the UI , users wants to see the header name in Chinese language for Chinese data and so on. 

So based on the table format I shared above, I need to create three straight tables based on three different headers. 

I would like to achieve something like this in the script while loading:

For English:

Load

Date,Language,Language Flag,Issue
16-05-2021,English,English,A

where LanguageFlag not in (CHHeader,Chinese,JPHeader,Japanese) 

Concatenate

For Chinese I want to load the columns like the below.. This one I am not sure how it can be transformed. Since the below header is coming under main header.. 

Date,Language,CHHeader,Issue
16-05-2021,Chinese,Chinese,B

On UI - Output should be

Sample Output:

Output 1: English

Date	Language	LanguageFlag	Issue
16-05-2021	English	English	A

Output 2: Chinese

Date	Language	CHHeader	Issue
16-05-2021	Chinese	Chinese	B

Output 3: Japanese

Date	Language	JPHeader	Issue
16-05-2021	Japanese	Japanese	C

[vinieme12]

---

@Nisha_Pra wrote:

I have lot of columns in the data set which will show the column name in Chinese language for Chinese data, in Japanese language for Japan data and for other languages column name will be English.

please post an sample that best represents your data

[Nisha_Pra]

Hi Vineeth,

Sample Data - Row No , 1,3,5 are headers coming from the same table. First row header is for English Language. Third row header is for Chinese language, Fifth row header is for Japanese language.

Date	Language	LanguageFlag	Issue	Comments	Support	RootCause
16-05-2021	English	English	No	Yes	Yes	Yes
Date	Language	CHHeader	客户代表正确识别了问题?	这是客户代表对应范畴内的问题吗?	エージェントが問題の根本的な原因について説明を行ったかどうか	客户代表对问题的根本原因进行了教育
16-05-2021	Chinese	Chinese	Yes	No	No	Yes
Date	Language	JPHeader	エージェントが正しい問題を特定したかどうか?	問題がサポートエージェントの範囲内かどうか?	客户代表对问题的根本原因进行了教育	エージェントが問題の根本的な原因について説明を行ったかどうか
16-05-2021	Japanese	Japanese	Yes	No	Yes	No

[vinieme12]

As below

temp:
Load * Inline [
Date,Language,LanguageFlag,Issue,Comments,Support,RootCause
16-05-2021,English,English,No,Yes,Yes,Yes
17-05-2021,English,English,No,Yes,Yes,Yes
Date,Language,CHHeader,客户代表正确识别了问题?,这是客户代表对应范畴内的问题吗?,エージェントが問題の根本的な原因について説明を行ったかどうか,客户代表对问题的根本原因进行了教育
16-05-2021,Chinese,Chinese,Yes,No,No,Yes
18-05-2021,Chinese,Chinese,Yes,No,No,Yes
Date,Language,JPHeader,エージェントが正しい問題を特定したかどうか?,問題がサポートエージェントの範囲内かどうか?,客户代表对问题的根本原因进行了教育,エージェントが問題の根本的な原因について説明を行ったかどうか
16-05-2021,Japanese,Japanese,Yes,No,Yes,No
19-05-2021,Japanese,Japanese,Yes,No,Yes,No
];

CrossTable(Field,Header,1)
Headers:
Load
pick(wildmatch(LanguageFlag,'CHH*','JPH*'),'Chinese','Japanese') as HeaderKey
,LanguageFlag as rn_LanguageFlag
,Issue as rn_Issue
,Comments as rn_Comments
,Support as rn_Support
,RootCause as rn_RootCause
Resident temp where Date='Date';

for Each lang in FieldValueList('HeaderKey')

Map_$(lang):
Mapping Load
Field,Header
Resident Headers
Where HeaderKey='$(lang)';

NoConcatenate

$(lang)_Data:
Load
Date as Date_$(lang),Language as Language_$(lang),LanguageFlag as rn_LanguageFlag ,Issue as rn_Issue ,Comments as rn_Comments,Support as rn_Support,RootCause as rn_RootCause
Resident temp
Where Language='$(lang)';

Rename fields using Map_$(lang);

next

NoConcatenate
English_Data:
Load * Resident temp Where Language='English';

drop table Headers,temp;
exit Script;

for some reason Japanese headers for Issue and RootCause won't get renamed, I guess there are some character limitations causing this