Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
hermannfloimayr
Contributor
Contributor

ASCII to char - Data from DB

 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.

Labels (1)
1 Solution

Accepted Solutions
pradosh_thakur
Master II
Master II

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

19 Replies
pradosh_thakur
Master II
Master II

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

can I use here a if rule?

ASCII.PNG

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

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

the sample is in the zip file at the top.

pradosh_thakur
Master II
Master II

I was asking for the output
Learning never stops.
hermannfloimayr
Contributor
Contributor
Author

The output from the screenshot schould be "Hermann"

ASCII: 072101114109097110110  Char: Hermann

pradosh_thakur
Master II
Master II

[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
Contributor
Contributor
Author

Thanks for your help!

But the script stopped at the [data]

!.PNG

pradosh_thakur
Master II
Master II

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.