Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kicchu465
Creator
Creator

Need Help in data Extract

Hi Experts,

I have a requirement for  extracting data in Qlik Sense.

My data file Contains as below

Field1Field2Field3Field4Field5Field6Apr-17May-17Jun-17Jul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19
AA1A2A3A4A5100101102103104105106107108109110111112113114115116117118119120121
BB1B2B3B4B5200201202203204205206207208209210211212213214215216217218219220221
CC1C2C3C4C5300301302303304305306307308309310311312313314315316317318319320321

 

The data gets added to data file month on Month, So My requirement is to extract Only the Latest Month Instead of Extracting all the months data.

My Output Should look like as below

Field1Field2Field3Field4Field5Field6Jan-19
AA1A2A3A4A5121
BB1B2B3B4B5221
CC1C2C3C4C5321

 

I Cannot use Addmonths function because I am not sure when the Data gets updated in the data file, So the data extraction should be dynamic.

If you can share your thoughts it would be very helpful.

Thanks,

Kishore

Labels (5)
3 Replies
sunny_talwar

One way to do this

Table:
CrossTable (MonthYear, Data, 6)
LOAD Field1, 
     Field2, 
     Field3, 
     Field4, 
     Field5, 
     Field6, 
     [42826], 
     [42856], 
     [42887], 
     [42917], 
     [42948], 
     [42979], 
     [43009], 
     [43040], 
     [43070], 
     [43101], 
     [43132], 
     [43160], 
     [43191], 
     [43221], 
     [43252], 
     [43282], 
     [43313], 
     [43344], 
     [43374], 
     [43405], 
     [43435], 
     [43466]
FROM
[Need Help in data Extract.xlsx]
(ooxml, embedded labels, table is Sheet1);

FinalTable:
NoConcatenate
LOAD Field1,
	 Field2,
	 Field3,
	 Field4,
	 Field5,
	 Field6,
	 Date(Num#(MonthYear), 'MMM-YY') as MonthYear,
	 Data
Resident Table;

DROP Table Table;

Right Join (FinalTable)
LOAD Max(MonthYear) as MonthYear
Resident FinalTable;

Wrote the script in QlikView, but should work the same way in Sense

kicchu465
Creator
Creator
Author

@sunny_talwar 

Thanks very muvh sunny for quick reply.

I am trying to do incremental Load here. 

In the first run the app should load all months data and from the next run it should pick only the latest month instead of reading all the months and concatenate with existing qvd. 

I tried doing preceding but cant get the logic right. 

Thanks

S k

 

jonathandienst
Partner - Champion III
Partner - Champion III

Perhaps like this:

Incremental:
LOAD Field1,
	Field2,
	Field3,
	Field4,
	Field5,
	Field6,
	Month,
	Month as Loaded,
	Value
From Incremental.qvd

T_Fields:
CrossTable(Month, Value, 6)
LOAD * 
From ...;

Concatenate(Incremental)
LOAD Field1,
	Field2,
	Field3,
	Field4,
	Field5,
	Field6,
	Month,
	Value
Resident T_Fields
Where Not(Exists(Loaded, Month));

DROP Table T_Fields;

You can't do a preceding load on top of a cross table load, so you have to do a load resident instead.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein