Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Hope this find the right person who can help me out
Source data: Text files which are generated automatically and can vary from 10-15 in number and will be dropped in to folder
Qlik View Application: We have created an application based on a standard source which contains set of 11 fields and relevant expressions based on this 11 fields in the front end , so the application is freezed
Issue : some times the source may not contain all the 11 fields and the data can have 8-9 fields,In that case I don't want to change the code in the Qlik View application and set the non available field values as 0
Ex: standard source data
Code | emp | sale | qty | sale type | model | Region | country | state | reporting | Month |
---|---|---|---|---|---|---|---|---|---|---|
2345 | Anand | laptop | 5 | Online | L-4567yh | Asia | India | Andhra pradesh | Sindhu | January |
5867 | Arun | desktop | 8 | Vendor | D-2456t | Asia | India | Karnataka | Sindhu | January |
8954 | Ajay | laptop | 4 | Institution | L-4567y | Asia | India | Uttar pradesh | Sindhu | January |
4698 | santosh | Mobile | 6 | Retail Outlet | insol-2 | Asia | India | Kerela | Sindhu | January |
6457 | Surya | Tablet | 2 | Online | 345ikte | Asia | India | Orissa | Sindhu | January |
Faulty Source data:
Code | emp | sale | qty | sale type | model | Region | Month |
---|---|---|---|---|---|---|---|
2345 | Anand | laptop | 5 | Online | L-4567yh | Asia | January |
5867 | Arun | desktop | 8 | Vendor | D-2456t | Asia | January |
8954 | Ajay | laptop | 4 | Institution | L-4567y | Asia | January |
4698 | santosh | Mobile | 6 | Retail Outlet | insol-2 | Asia | January |
6457 | Surya | Tablet | 2 | Online | 345ikte | Asia | January |
Now in the faulty source data , fields Region, country, state , reporting are not available
So if i Load the Qlik view application it will throw an error as field not found so inspite of that error I want to load the application as is with a dummy value like 0 in to that field .
Thanks ahead.
Anand.
Hi Anand,
Try:
[Raw Data]:
LOAD *
FROM
[https://community.qlik.com/thread/228627]
(html, codepage is 1252, embedded labels, table is @1);
Concatenate([Raw Data])
LOAD *
FROM
[https://community.qlik.com/thread/228627]
(html, codepage is 1252, embedded labels, table is @2);
NoConcatenate
Data:
LOAD
if(isnull(Code),'Code Missing',Code) as Code,
if(isnull(emp),'Emp Missing',emp) as emp,
if(isnull(sale),0,sale) as sale,
if(isnull(qty),0,qty) as qty,
if(isnull([sale type]),'Sale Type Missing',[sale type]) as [sale type],
if(isnull(model),'Model Missing',model) as model,
if(isnull(Region),'Region Missing',Region) as Region,
if(isnull(country),'Country Missing',country) as country,
if(isnull(state),'State Missing',state) as state,
if(isnull(reporting),'Reporting Missing',reporting) as reporting,
if(isnull(Month),'Month Missing',Month) as Month
Resident [Raw Data];
Drop Table [Raw Data];
good luck
Andrew
I am guessing the data is coming from different files. I would suggest looking into concatenation of the data using for loops instead of doing a wild card load here.
Hi Anand,
in your script load the faulty source data like this:
FaultySource:
Load
Code,
emp,
sale,
qty,
sale type,
model,
Region,
Month,
'-' as country,
'-' as state,
'-' as reporting
From yourFaultySource;
Then it will automatically concatenate to your standard source.
Hope this helps.
Burkhard
Hi Burkhard,
Thanks for your reply.
That works when I know that in one file if that 3 fields are missing and then can load them as dummy from faulty source
But in my case say 4 source files are there
source1 may not have country
source2 may not have Region
source3 may not have reporting
source4 may have all fields
any my script in the QV application is ready as to load all the fileds from 4 files
Load
Code,
emp,
sale,
qty,
sale type,
model,
Region,
Month,
country,
state,
reporting
From yourFaultySource;
please let me know if i couldn't make you understand the issue. wil try to post more precise example with original data.
Anand
I would suggest to create a dummy table.
Then load in a loop all files from your source folder.
Use * to load all fields, independend from the number of fields.
Data:
load * inline [
Code
];
for each file in filelist ('yourfolder\*.csv')
concatenate (Data)
load *
from '$(filelist)' (settings from the sourcedata type)
;
next
Regards
Hi Anand,
Try:
[Raw Data]:
LOAD *
FROM
[https://community.qlik.com/thread/228627]
(html, codepage is 1252, embedded labels, table is @1);
Concatenate([Raw Data])
LOAD *
FROM
[https://community.qlik.com/thread/228627]
(html, codepage is 1252, embedded labels, table is @2);
NoConcatenate
Data:
LOAD
if(isnull(Code),'Code Missing',Code) as Code,
if(isnull(emp),'Emp Missing',emp) as emp,
if(isnull(sale),0,sale) as sale,
if(isnull(qty),0,qty) as qty,
if(isnull([sale type]),'Sale Type Missing',[sale type]) as [sale type],
if(isnull(model),'Model Missing',model) as model,
if(isnull(Region),'Region Missing',Region) as Region,
if(isnull(country),'Country Missing',country) as country,
if(isnull(state),'State Missing',state) as state,
if(isnull(reporting),'Reporting Missing',reporting) as reporting,
if(isnull(Month),'Month Missing',Month) as Month
Resident [Raw Data];
Drop Table [Raw Data];
good luck
Andrew
Maybe something like this
OldData:
LOAD * INLINE [
Region,Sales ,Month,Year
A,10 , Jan,2016
B,20 , Feb,2016
C,30, Mar,2016
];
NewData:
LOAD * INLINE [
Region,Sales
A,10
B,20
C,30
];
for i = 0 to 4
Colfind:
load
if( OldCol<>comingCol ,OldCol) as OldCol
;
load
FieldName('$(i)','OldData') as OldCol ,
FieldName('$(i)','NewData') as comingCol
AutoGenerate(1) ;
NEXT
Colfindop:
load Concat(DISTINCT '0 ' & ' as ' &OldCol,',') as vars
resident Colfind
Where len(OldCol)>1;
let VAdd = Peek('vars',0,'Colfindop');
NewData1:
load * ,$(VAdd)
Resident NewData ;
DROP Table NewData;
That was brilliant and it worked, thanks for the quick help
Anand.
Hi Anand,
We're glad to see you were able to solve your issue. Please take a moment to Mark Replies as Correct or Helpful to give points to those who helped and to mark the thread as Answered.
Regards,
Qlik Community Team
Hi Jeremy Latimer,
I couldn't find the "Correct Answer" option anywhere on this page as per your link, could you please help me get that option enabled . So that I will be happy to mark it as correct Answer. Marking them Helpful for the time being.
Anand