topic Re: Add 'total' name to all in QlikView

Add 'total' name to all

Anonymous — Fri, 28 Aug 2015 19:42:36 GMT

Hi guys,

I'd like to add 'Name' to all records in load script.

MyFile:

LOAD respondentID,

date,

CleanedText_FTC_Purchase as CleanedText,

[FTC_Purch concepts] as Concept,

OSAT,

OSAT_TOTAL_FTC_Purchase as "OSAT TOTAL",

OSAT_STD_TOTAL_FTC_Purchase as "OSAT STD TOTAL",

[Task Completion],

POV,

Product,

[Type of mouse],

[Type of keyboard],

'Purchase' as Type Resident FTC_Purchase_EXPORT;

Concatenate

LOAD respondentID,

date,

CleanedText_FTC_research as CleanedText,

FTC_research_concepts as Concept,

OSAT,

OSAT_TOTAL_FTC_Research as "OSAT TOTAL",

OSAT_STD_TOTAL_FTC_Research as "OSAT STD TOTAL",

[Task Completion],

POV,

Product,

[Type of mouse],

[Type of keyboard],

'Research' as Type Resident FTC_research_EXPORT;

How can I do it?

Thanks,

Re: Add 'total' name to all

marcus_sommer — Fri, 28 Aug 2015 19:51:05 GMT

I'm not sure if I understand it right. Mostly will be this done with a table-prefix created per qualify-statement - if you want instead a suffix you could use a rename-statement with mapping.

- Marcus

Re: Add 'total' name to all

Anonymous — Fri, 28 Aug 2015 19:55:44 GMT

What I'd like to add it's a new variable which will show from what source I took it.

Thanks,

Re: Add 'total' name to all

Anonymous — Fri, 28 Aug 2015 19:56:49 GMT

"purchase" and "research" it's my text. I don't have them in the sources.

Re: Add 'total' name to all

sasiparupudi1 — Fri, 28 Aug 2015 20:12:11 GMT

what is the issue you are having? please provide sample data and the problem you are facing

Re: Add 'total' name to all

qlikviewwizard — Sat, 29 Aug 2015 04:30:02 GMT

Hi,

Please attach the sample qvw with the data. Thank you.

Re: Add 'total' name to all

marcus_sommer — Sat, 29 Aug 2015 09:39:46 GMT

If you are loading from files you could use file-functions like filebasename() as Source to specify your source. By resident-loads or sql database loading you will to set this manually unless you used variables for the table-names which could be used instead the filebasename().

- Marcus

Re: Add 'total' name to all

Anonymous — Mon, 31 Aug 2015 13:45:25 GMT

I have 2 files. I want to concatenate them and add a new variable which I called "Type" The "Type" variable should show from what the file I took.

Re: Add 'total' name to all

sasiparupudi1 — Mon, 31 Aug 2015 13:58:18 GMT

why dont you add an additional coulmn in your excel documents called type with the required values?

Re: Add 'total' name to all

Anonymous — Mon, 31 Aug 2015 14:00:38 GMT

It's just an example. Original files I got from different sources.

Re: Add 'total' name to all

sasiparupudi1 — Mon, 31 Aug 2015 14:07:07 GMT

As Marcus suggested, why dont you use filebasename?

LOAD respondentID,

date,

Concept, filebasename() as Type

FROM

C:\sasi\qv\research.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD respondentID,

date,

Concept,filebasename() as Type

FROM

C:\sasi\qv\Purchase.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Add 'total' name to all

Anonymous — Mon, 31 Aug 2015 14:13:14 GMT

The file name has different name It comes from different sources throw join command

Re: Add 'total' name to all

Anonymous — Mon, 31 Aug 2015 14:36:24 GMT

It didn't work.

FTC_Support_EXPORT:

NOCONCATENATE

LOAD respondentID, submitDateTime as date Resident Respondents;

RIGHT JOIN LOAD respondentID, CleanedText_FTC_support_OE as CleanedText_FTC_Support, [FTC_support_OE] as FTC_Support, FileBaseName() as Type Resident FTC_support_OE;

LEFT JOIN LOAD respondentID, [FTC_support_OE concepts] as FTC_Support_concepts Resident FTC_support_OE_concepts;

LEFT JOIN LOAD respondentID, OSAT Resident OSAT;

LEFT JOIN LOAD respondentID, OSAT_TOTAL_FTC_Support, OSAT_STD_TOTAL_FTC_Support Resident OSAT_TOTALS;

LEFT JOIN LOAD respondentID, [Task Completion] Resident TC;

LEFT JOIN LOAD respondentID, POV Resident POV;

LEFT JOIN LOAD respondentID, Product Resident Product;

LEFT JOIN LOAD respondentID, [Type of mouse] Resident [Type of mouse];

LEFT JOIN LOAD respondentID, [Type of keyboard] Resident [Type of keyboard];

The variable is empty

Re: Add 'total' name to all

sasiparupudi1 — Mon, 31 Aug 2015 16:06:03 GMT

Please post your entire script so that we can better advise you

Re: Add 'total' name to all

Anonymous — Mon, 31 Aug 2015 16:10:13 GMT

I did it.

I added number for each load file

LOAD respondentID, submitDateTime as date, 4 as Type Resident Respondents;

then concatenate all of them and recode number to text

it works, but probably it's not good way