Solved: ASCII to char - Data from DB - Qlik Community

hermannfloimayr · ‎2019-02-14

Hi!

I should show in the QLIK Sense APP the name of the machine operator. We get the datas from a OPC UA server and store this data in a MySQL DB. The system cannot store characters. So we translate the characters to ASCII. Eatch single character to 1 entry. Now in QLIK it should trasnlate back to characters. The result should be a name. (combination of TagID 70,71,72,73,74,75,76,77,...85) I got the solution the translate the ASCII to Char in this post. But I have no idea to use this way in my situation.
Attaches example form my DB.

pradosh_thakur · ‎2019-02-18

Ohk. I see. Try below

[esync_tagshistory]:
FIRST 100 LOAD 
	[TagId] AS [Id-TagId],
	[_date],
	[msec],
	[Val] AS [esync_tagshistory.Val],
	[Quality] AS [esync_tagshistory.Quality];
SELECT TagId, 
	`_date`, 
	msec, 
	Val, 
	Quality 
FROM esync.`esync_tagshistory`;



noconcatenate
[data]:
Load [_date],concat([esync_tagshistory.Val],'',[Id-TagId] ) as [esync_tagshistory.Val_new]
 group by [_date];
LOAD
       [Id-TagId],
	[_date],
	[msec],
	[esync_tagshistory.Val],
	 [esync_tagshistory.Quality];
	if([Id-TagId] >= 70 and [Id-TagId] <=85, chr([esync_tagshistory.Val]),[esync_tagshistory.Val]) as [esync_tagshistory.Val]
	
resident [esync_tagshistory]
where [Id-TagId] >= 70 and [Id-TagId] <=85
order by [_date],[Id-TagId] asc;

Learning never stops.

View solution in original post

pradosh_thakur · ‎2019-02-14

CharMap:
Mapping LOAD
RecNo() - 1 AS Asciicode,
Chr(RecNo() - 1) AS Char
AutoGenerate 256;

Data:
LOAD *,
MapSubString('CharMap', field_to_convert) as new_stringfield

from 

<table>

I have modified the script written by @mdmukramali in the previous post. Give it a try.

Learning never stops.

hermannfloimayr · ‎2019-02-14

can I use here a if rule?

Only field Val should converted if the TagID is 70-85

And the output should be a combination from alle TagIDs 70-85 in one word and only the have the same _date Timestamp.

pradosh_thakur · ‎2019-02-14

Can you give us a sample output or an example o that there is no to and fro conversation for requirement.

Learning never stops.

hermannfloimayr · ‎2019-02-14

the sample is in the zip file at the top.

pradosh_thakur · ‎2019-02-14

I was asking for the output

Learning never stops.

hermannfloimayr · ‎2019-02-14

The output from the screenshot schould be "Hermann"

ASCII: 072101114109097110110 Char: Hermann

pradosh_thakur · ‎2019-02-14

[data]:



LOAD
	[TagId],
	[_date],
	[msec],
	if([TagId] >= 70 and [TagId] <=85, chr([Val]),[Val]) as [Val],
	[Quality]
 FROM [lib://Downloads/data.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq)



;

 NoConcatenate
  Load [_date],[Quality],concat([Val]) as [Val]
 group by [_date],[Quality];
Load * Resident [data]
where [TagId] >= 70 and [TagId] <=85
order by [_date],[TagId] asc
;

Learning never stops.

hermannfloimayr · ‎2019-02-15

Thanks for your help!

But the script stopped at the [data]

pradosh_thakur · ‎2019-02-15

on line 80 you have used the alias name [Id-TagId] and [esync----value]. The table is not relaoded yet so you have to use the parent name in the same table. Use TagId and VAl insted.

Make sure there is no unwanted character before [data] ndn check for spaces as well..

Learning never stops.